Risc World 3

home *** CD-ROM | disk | FTP | other *** search

/ Risc World 3 / Risc World 3.iso / SOFTWARE / ISSUE4 / POWERBASE / OPRO / Manual (.txt) < prev next >

Wrap

Ovation Pro Document | 2001-12-06 | 630KB | 7,225 lines

OvationPro (21-Sep-00) Generic Black White Transparent Green Magenta Yellow Registration RGB000000 RGBFFFFFF Trinity.Medium.Italic System.Fixed Trinity.Bold.Italic Homerton.Bold Homerton.Bold.Oblique Homerton.Medium Trinity.Medium Trinity.Bold Corpus.Medium BodyText Main Heading Sub-Heading 1in indent Hanging indent Contents2 For indexing Effect17 Sub-SubHeading Effect22 Page number Corpus.Medium HalfSpace Effect663 Centre Homerton.Medium NoGap SubSubSub Justify Contents0 Contents1 Trinity.Medium Effect946 Effect724 Index Footnote Italic Index layout Effect12945 System.Fixed Effect12948 Underline Effect417 Effect429 Effect435 /Wh3M HtI", sh"< _A&X! EH*D" ME.H# :;9(& NS~9P& Z&$<@' ,B><( {filename} P{pagenumber} {datetime} DrawPlus Database for Acorn computers with RISC OS 3.1 or later Derek and Steven Haslam Manual revised May 2001 to comply with v. 8.05 of Powerbase Database for Acorn computers with RISC OS 3.1 or later Derek and Steven Haslam Manual revised May 2001 to comply with v. 8.05 of Powerbase Contents Ch 1 Introduction to Powerbase Description and installation 1.1.1 Hardware requirements 1.1.2 Installation 1.1.3 Disclaimer Starting and ending a work session 1.2.1 Loading Powerbase 1.2.2 Opening a database 1.2.3 Closing a database 1.2.4 Quitting Powerbase Obtaining help 1.3.1 Using the Acorn Help application 1.3.2 Using the Helpreader text Ch 2 Browsing and Editing Subfiles, record numbers and keys 2.1.1 Subfiles 2.1.2 Record numbers 2.1.3 Moving about the database 2.2.1 Browsing 2.2.2 Moving to related records 2.2.3 Changing keys 2.2.4 Changing subfiles 2.2.5 Using more than one subfile 2.2.6 Naming subfiles Searching for a record 2.3.1 Searching by key 2.3.2 Searching by record number 2.3.3 Searching by filter Editing the database fundamental operations 2.4.1 Adding new records 2.4.2 Deleting records 2.4.3 Altering existing records 2.4.4 Undoing mistakes Editing the database special features 2.5.1 Using a template 2.5.2 Copying fields 2.5.3 Copying an entire record 2.5.4 Choosing the field where editing starts 2.5.5 Changing many records at once 2.5.6 Moving or deleting many records at once 2.5.7 Hiding sensitive data External fields 2.6.1 Linking files to the fields 2.6.2 Editing External fields 2.6.3 Clearing and exporting field contents 2.6.4 Editing scrollable lists Ch 3 Printing from the Database Output destination 3.1.1 The Window destination 3.1.2 The File destination 3.1.3 The Printer destination Print formats 3.2.1 Horizontal 3.2.2 Vertical 3.2.3 Table 3.2.4 Label What types of field can be printed? Specifying which fields to print 3.4.1 Saving print selection files 3.4.2 Default selection Specifying which records to print 3.5.1 The construction of search formulae 3.5.2 Numeric and other special fields in search formulae 3.5.3 Using wild-cards in search formulae 3.5.4 Comparing the contents of two fields 3.5.5 Saving search formulae for re-use Query by example 3.6.1 What is QBE? 3.6.2 QBE vs SF Other features of the Query panel and Match window 3.7.1 Printing records from more than one subfile 3.7.2 Including record number, key and subfile number Marking records for inclusion or exclusion 3.8.1 Single records 3.8.2 Groups of records Printing single records The Print options window 3.10.1 Saving print options files Field analysis reports Subsidiary indices and printing speed Ch 4 Creating a New Database Creating the database application shell Designing the record layout 4.2.1 Simple field creation 4.2.2 Deleting, inserting and re-ordering fields. 4.2.3 Moving and re-sizing the bounding box 4.2.4 More about tags and descriptors 4.2.5 Other types of Editable field 4.2.6 Scrollable lists 4.2.7 Check-box fields 4.2.8 External fields 4.2.9 Computed fields 4.2.10 Stamp fields 4.2.11 Button fields 4.2.12 Mandatory fields A short-cut to a working database Specifying the database size Specifying the primary key 4.5.1 General procedure 4.5.2 Some illustrative examples 4.5.3 Using more than one field in a key 4.5.4 Other matters concerning keys Building the empty database Renaming a database Ch 5 Input Validation and Validation Tables Character validation Validation tables Creating a validation table Linking tables to fields 5.4.1 The Replace on entry feature 5.4.2 When to turn off the Exact match switch Displaying validation tables The validation table menu Loading validation tables Including validation table data in printouts Entering validation table data into a record Ch 6 Performing Calculations Calculated fields 6.1.1 Simple calculations using Numeric fields 6.1.2 Making calculations retrospective 6.1.3 Calculations using non-numeric fields (!) 6.1.4 Calculations involving times 6.1.5 Calculations involving dates Composite fields User functions Calculations on a column of a report Ch 7 Using extra indices Indexing a field Automatic saving of indices Ch 8 Using CSV files What are CSV files? Setting the CSV options Exporting data as a CSV file 8.3.1 Scrollable lists and CSV files Using CSV files to import data 8.4.1 Ensuring that the correct options are selected 8.4.2 Directing imported data to the correct fields 8.4.3 Importing data from plain text files 8.4.4 What if the imported data won t fit? Using CSV files to modify existing records Creating a new, working database from a CSV file Ch 9 Mail-Merging with Impression and Ovation Mail-merging with Ovation* 9.1.1 Preparing an Ovation document for mail-merging 9.1.2 Merging the data from Powerbase Mail-merging with Impression 9.2.1 Preparing the Impression document 9.2.2 Merging the data Mail-merging with other programs Ch 10 Utilities Changing the Primary Key Adjusting the record format Changing the record format Merging two databases Changing the Database Length Inspecting and balancing index trees Finding duplicate primary keys Ch 11 Passwords and related matters Levels of protection 11.1.1 Individual I.D.s and passwords Options selected from the password-setting window 11.2.1 Duplicate primary keys Logging database changes Ch 12 Script Files Ch 13 Subset databases Creating a subset Using a subset to shorten a database Ch 14 Customising Powerbase Overall control of the database Defining the function keys CSV options Preferences 14.4.1 Separators in date and time fields 14.4.2 Wild-cards 14.4.3 Option switches 14.4.4 Save indices (Default: Manual) 14.4.5 Start editing at 14.4.6 Application for ImpulseII data-merging 14.4.7 Save choices Colours of key fields and table-linked fields Config files 14.6.1 Pathnames for External file objects The Messages file Appendix A Powerbase as an Impulse server Description: Appendix B Keystroke equivalents Index Ch 1 Introduction to Powerbase Description and installation Powerbase is a flat-file database management system for use with Acorn 32-bit RISC computers. Versions from 6.xx onward are only usable with RISC OS 3.1 or later. If you are using RISC OS 2 you will require a different version of Powerbase which has a version number of the type 5.xx. Many of the features described in this manual are not present in that version of Powerbase but appropriate documentation in the form of text-files can be provided. This manual was fully revised in December 2000 to comply with v. 8.00 of Powerbase and users (other than RISC OS 2 users) of earlier versions are strongly advised to upgrade to this version, which incorporates many new features and is fully compatible with StrongArm and RISC OS 4.02. The software, documentation and sample databases may be downloaded from our website at www.pendle.ukgateway.net/ Powerbase, versions and compatibility 1.1.1 Hardware requirements It is quite feasible to use Powerbase on a 1Mb machine with a single floppy disc drive and, if your database runs to only a few hundred short records (e.g. an address-book), you might find such a system quite adequate for your needs. You will, however, find report creation rather slow when working from a floppy disc and a hard disc is strongly recommended, even for a small database; for large databases it is essential. If you have to work from floppies try to allocate enough memory to the RAM disc to copy the whole database onto it before you start work. This will speed up searches and reports enormously. 1.1.2 Installation Installing Powerbase Most users will receive Powerbase in the form of an ArcFS archive. You are advised not to run the program directly from the archive (although that is possible) but to de-archive it as follows. Run the supplied read-only version of ArcFS and double-click on the Powerbase archive. The archive will open to reveal the !Powerbase application which should be dragged to a suitable directory window. It is suggested that you also, by the same method, de-archive the sample databases e.g. !Elements, !Boxes, !Friends from the Examples archive if you intend to experiment with them or work through either of the tutorials. The tutorials are provided in a separate booklet. If databases are completely new to you we recommend you to work thought the Quick Tutorial which is based on the simple address-book database !Friends. If you are somewhat more experienced but are using Powerbase for the first time you will probably find Tutorial , using the !Elements database, more appropriate. 1.1.3 Disclaimer No warranty, express or implied, is made about the suitability of Powerbase for any purpose. We cannot be held responsible for any loss or damage due to the use of this software. All enquiries, requests for upgrades etc, should be directed to: Powerbase support 112, Keighley Road Colne Lancashire BB8 0PH Tel: (01282) 866835 email: quercus@ukgateway.net email address for Powerbase Support website: www.pendle.ukgateway.net/ When requesting an upgrade please enclose four first-class stamps. website for information & upgrades Starting and ending a work session 1.2.1 Loading Powerbase Double-click on the Powerbase icon in a directory window and the program will load and place its icon on the iconbar. The words No data will appear under the icon indicating that no database is open yet. 1.2.2 Opening a database A database may be opened for use by either of the following methods: Dragging the database icon onto the Powerbase icon on the iconbar. Double-clicking on the database icon. This is the more usual method and may be used even if Powerbase is not already loaded, as long as it has been by the filer. Whether you get immediate access to the database depends on whether or not it is password protected. If no password s have been defined (as is the case with all the sample databases on the distribution disc) the main record window will open and the name of the database will appear under the Powerbase icon. password -protected database will display the Access window in the middle of the screen requesting you to enter a password and perhaps even a personal user I.D. as well. The window has an button which should be clicked with SELECT when you have entered the required information. There is also a Cancel button which removes the window from the screen without further action. Passwords and I.D.s are case-sensitive and, if incorrectly entered, result in either a Password not known Access denied message. After three successive incorrect entries Powerbase will close down and remove itself from the iconbar. 1.2.3 Closing a database There are three ways of closing a database: Open another database, either by double-clicking or dragging to the Powerbase icon. Choose from Powerbase's iconbar menu. This also quits Powerbase itself. Choose Close database from the iconbar menu. This still leaves the Powerbase application running. The wording underneath reverts to No data If you have made any change to the database while it was open the disc will be automatically updated so that your changes are not lost, regardless of which of the above three methods of closure is used. 1.2.4 Quitting Powerbase Choose from the iconbar menu. You need not specifically close your database first; any changes made to it will be saved before Powerbase quits. Obtaining help 1.3.1 Using the Acorn application When you are first becoming acquainted with Powerbase you may find it useful to load Acorn application (from on the iconbar). Resize the Help window, if you wish, and move it to some convenient place on the screen. Moving the mouse pointer over the various windows, icons and many of the menus displayed by Powerbase will then give a brief description of the purpose of each. 1.3.2 Using the Helpreader text Further help is available from the option which appears on both the main menu and the iconbar menu. Choosing this option displays a text file which you can navigate by means of small orange buttons included in the text. Clicking with SELECT on such a button takes you directly to the part of the text referred to. Double-clicking with ADJUST takes you back to where you were when you last clicked on a button. Clicking MENU over the text window displays a menu giving an alternative means of access to specific parts of the text. The text has deliberately been kept fairly short and is in no way a substitute for this manual; it is intended to provide basic information only. Help available within Powerbase : ~P@ @| ~P@ There is one circumstance where this is not entirely possible and that is when you are running a database from the RAM disc . Switching off the computer or quitting the RAM disc will, of course, result in loss of data. When you close a database on the RAM disc therefore, Powerbase will warn you to back it up to a more permanent medium. Thanks are due to Ben Summers who wrote the Helpreader application used to crea te and display this text file and who kindly gave permission for it to be distributed with Powerbase Ch 2 Browsing and Editing When you open a database you will normally see two windows. The record window displays one data record at a time. Each item of data in the record occupies a rectangle called a field keypad contains buttons for moving around the database, adding and deleting records, searching for records etc. It may be toggled between full size and a display of the first two rows of buttons only. You can also suppress the keypad and put some or all of its buttons on the record window itself (see 4.2.11 ). The sample database Boxes illustrates this. In this chapter we will assume that you are using the keypad. Both windows may be moved to whatever position on the screen you find most convenient and the record window may be re-sized. When you close the database the position of the keypad and the position and size of the record window are stored so that they will look the same next time you open the database. Most of the keypad functions have keystroke equivalents provided by the numbered function keys F11) and experienced users might want to use these instead of the mouse. The relevant keystroke s are given in the text where appropriate but note that these are default values and may be redefined if so desired. Many other actions have equivalent Ctrl sequences which can t be redefined. See Appendix B for a complete list. Subfiles record numbers and Look at the title-bar of the record window. Three pieces of information are displayed; the subfile number , the record number and the of the record. Since we will make frequent reference to these three terms it will be as well to explain at the outset what each one means. Subfiles, explanation of 2.1.1 Subfiles Suppose you have a bookshelf, capable of holding 100 books, and six partitions, numbered 0 5, which you can use to divide it into sections. The partitions are, to begin with, all stacked together at the left-hand end of the shelf. You may leave them like this if you wish and fill up the shelf with an unbroken run of 100 books. If, however, the books fall into well defined categories you might prefer to use the partitions to divide the shelf up into sections. There are no restrictions on where you can place the partitions, or how many books can fall into each section of shelf, so long as you don t exceed a total of 100 books. In a similar way every Powerbase database is able to use up to six subfiles numbered 0-5. When you open the database you are looking at subfile 0 and you might have no occasion to bother with the others at all, in which case they can be ignored. Like the bookshelf partitions subfiles are there for you to use or not use depending on what your requirements are. The database remains the same size whether you use one subfile or all six and the number of records in a subfile is limited only by the defined size of the database. Section 2.2.4 suggests instances in which you might find it advantageous to use more than one subfile. 2.1.2 Record numbers Every record in the database has a number which specifies its position in a file called Database . As you step through the records (see ) you will probably find that the record numbers shown in the title-bar look quite random. Record numbers reflect the order in which records were originally entered into the database, not the order in which you see them while browsing. The latter is determined by the key. Database file 2.1.3 key, explanation of A key is a group of letters or other characters derived from one or more of the data fields in a record and used to identify that record. The record number identifies the record in a sense, of course, but record numbers are of limited use because they only reflect the order in which the records were entered. Keys, on the other hand, are derived from the actual data and are stored in a special table called an index . The keys in the index are ordered , either alphabetically or numerically and can therefore be used to examine the records in an order which is related to the data. To use an analogy with books again, you can look up a key in an index and the index tells Powerbase what it is on, i.e what is the number of the record to display. A database may have several indices built from keys based on different fields in the record but there be at least one which we call the primary key index . When you come to create a database of your own you will find that Powerbase insists that you define the primary key before you can use the database. Subsidiary keys may be defined at any time; whether you use them or not is up to you. Look at the field and at the key itself in the title-bar and you will see that the two are related. You can recognize the key field on which the currently-active key is based by its descriptor (label) which will be red. Key fields for other keys have blue descriptors. Non-key fields have black descriptors. The primary key field is further identified by the background colour of the data rectangle which is pale yellow instead of white like the other data fields. Colours used to identify key fields Moving about the database 2.2.1 Browsing Six buttons on the top row of the keypad move you about within the current subfile. From left to right their actions when clicked on with SELECT are: Keystroke equivalent First record Ctrl F1 First record button Previous record Previous record button 10 records back Fast rewind Shift F1 Fast rewind button Rewind button 10 records forward Fast forward Shift F2 Fast forward button Next record Next record button Final record Ctrl F2 Final record button The number of records forward or back for actions (3) and (4) is determined by the number ( Fast forward interval) in the writable icon between the associated keypad buttons. The terms first, previous, back, forward, next and final refer to the order of records as determined by the in the index . The file wraps around so after the final record clicking the Next record button takes you to the first record. If you display the first record, clicking the Previous record button takes you to the final one. In accordance with RISC OS conventions clicking with ADJUST instead of SELECT reverses these actions so that ADJUST over the Next record button, for instance, takes you to the previous record rather than the next. This allows you to move about the database with fewer movements of the mouse. Stop button Play button Fast forward interval The buttons at the start and end of the second keypad row resemble the Play Stop button s of a tape or video recorder and it will be convenient to refer to them by these names. Play (F11 ) causes Powerbase to run through the records, displaying each one briefly, and Stop (Shift F11 ) halts the process. Closing the record window has the same effect as . If Play is clicked with SELECT records are displayed in forward order. Using ADJUST displays them in reverse order. You may swap the order at any time by a further click on . The time for which each record remains on screen may be altered by changing the Fast forward interval (see above); the larger number you type into this icon the longer each record will remain on screen. At the bottom of the keypad are four bookmark icons which let you mark specific records for rapid retrieval. Click on one with SELECT and the number of the current record appears in the icon. Clicking with SELECT on a bookmark icon which contains a record number will return you to that record. Click with ADJUST to clear the marker. Lookup function Related records, accessing 2.2.2 Moving to related records If you Shift/double-click on a field with SELECT, and the contents of the field match the current key of another record, that record will be displayed. We call this the lookup function. It will also work if a field contains a comma separated list of keys. Shift/double-click on any one of these keys takes you to the relevant record. You could experiment with this using the Elements database. Look up, say, SODIUM, enter in one of the Notes fields the primary keys of the other Group 1 metals, i.e. lith,pota,rubi,caes,fran. You can now access the records of these five elements from SODIUM s record. 2.2.3 Changing keys Two buttons in the middle of the second row of the keypad bear a symbol resembling a key. They enable you to cycle through the available indices in opposite directions ( Shift F4 and Shift F5 respectively). ADJUST, as usual, reverses the action of SELECT and wrap around again applies. If no subsidiary indices have been defined these buttons will have no effect. Next key button Previous key button 2.2.4 Changing subfiles Three buttons in the centre of the third row of the keypad have the following effect when clicked with SELECT: Go to previous subfile Previous subfile button Rotate subfiles Ctrl F4 Rotate subfiles button Subfile rotation Go to next subfile Next subfile button (1) and (3) change the current subfile to the previous one in sequence and the next in sequence respectively. As with the corresponding buttons for moving from record to record and key to key Next subfile from subfile 5 takes you to subfile 0 and Previous subfile from 0 takes you to 5. ADJUST reverses these actions. Rotate renumbers the subfiles so that subfile 0 becomes 1, subfile 1 becomes 2 etc. Subfile 5 becomes 0. Records which were previously in, for example, subfile 2 will then be found in subfile 3. As this is quite a drastic change you will be asked to confirm that it s what you really want before Powerbase proceeds. 2.2.5 Using more than one subfile Multiple subfiles, use of Powerbase database consists, as stated earlier, of six subfiles numbered 0-5. You may use subfile 0 (which is the one used by default) and ignore the others if you wish, but there are occasions when users might find two or more subfiles very useful. If, for example, you are maintaining a set of pupil records in a school you could assign one subfile to each year. At the end of a school year the promotion of Year 1 pupils to Year 2, Year 2 to Year 3 etc can be brought about by a single click on the Rotate button . (In an 11-16 school this would put the leavers into subfile 0, from which they could later be removed.) A more common use of subfiles is to use subfile 0 to hold the in-use records and subfile 1 as a dumping ground for records which you want out of the way but don t want to lose permanently. Powerbase is actually used in this way in a Further Education college where the applications file for a course is built up in subfile 0. On enrolment day records are updated to reflect students subject choices and all the records for applicants who have not appeared are moved into subfile 1. The enrolled students in subfile 0 are then timetabled and class lists are printed. During the first week of the course there is a trickle of late enrolments which simply involve retrieving the required records from subfile 1 and reinstating them in subfile 0. The facilities for moving records, singly or in batches, from one subfile to another are described in 2.4.2 2.5.6 . You can, of course, also select a subfile and type new records directly into it. 2.2.6 Naming subfiles Subfiles, naming To give subfiles meaningful names click MENU over the record window and go to the Miscellaneous submenu. From there Name subfile leads to a writable menu item into which you may type a name of up to 20 characters. This then becomes the name of the current subfile and will be saved on closing the database. Searching for a record s all very well being able to move about the database like flipping through the pages of a book but how do we find a specific record? Powerbase provides three ways of doing this: you can search for the record by , by record number or by using a filter 2.3.1 Searching by key Records, retrieving by key The icon bearing a record card and a question mark is the Searc h button ). When clicked with SELECT the Search window opens to the right of the keypad . Type the key of the required record into the writable icon and click on the Find button or type Return. To see how the key is constructed from the key field click MENU over the record window and choose Index => Show details Ctrl K ). This displays a window describing the key structure of the currently-active index If the key exists in the index the record will be found and displayed. If the key doesn t exist Powerbase beeps and displays the nearest matching record with the key field flashing . If the database is a small one with which differ greatly from record to record then the displayed record may not look to be very near at all. If you typed Return or used SELECT on the Find button the Search window will be closed. If you want to search repeatedly it is more convenient to have the window stay on screen and this can be achieved by clicking on Find with ADJUST. If you do this you will notice that the icon above the Find button displays the number of matches found Clicking on the icons (the up and down arrowheads) in the Search window allows you to search an index other than the current one. The index used affects the search only; clicking the browse buttons on the keypad will show you that the current index has not been changed. A search carried out as described looks only at the current subfile. If you suspect that the record you require might be lurking in some other subfile you can force all six to be searched. This can be achieved by using ADJUST ( Shift F8 ) instead of SELECT on the keypad Search button. When the Search window appears you will see that the subfiles radio button is selected instead of This subfile . The radio buttons enable you to change your mind after the window has been opened. Records, searching all subfiles for searching on a numeric key you must enter a number of exactly the right value. If, however, the key is alphabetic you may use just the first few letters if they are sufficient to distinguish the key from others. There are two other buttons on the Search window restores the last key entered into the writable icon and Cancel simply removes the window from the screen without further action. 2.3.2 Searching by record number Records, retrieving by record number You may type a record number, preceded by # (e.g. #1054), in the Search window instead of a key. Provided the record is in use Powerbase will find and display it, regardless of which subfile it is in. Bear in mind that, if the record is found in some other subfile than the current one, that subfile will become the current one. If the record has been deleted or has never been assigned a Record #xxx is not in use message is displayed. 2.3.3 Searching by filter Records, filtering Filter switch ( Ctrl F8 ) is in the middle of the last row on the keypad . Selecting it brings up the Filter window which contains a writable icon into which you can type a search formula (see ). You should then click on the Filter button in the Filter window or, alternatively, type Return. The keypad buttons used for browsing will now only display records which match the search formula , all non-matching records being filter ed out. Deselecting the Filter switch closes the Filter window and restores normal keypad operation. So does clicking Cancel on the Filter window. The Close button simply closes the Filter window but leaves the filter in effect useful if you want the window out of the way once you ve set up a filter. Editing the database fundamental operations 2.4.1 Adding new records The icon bearing a record card and a plus sign is the Add record button ). Clicking with SELECT displays a blank record for you to fill in. The subfile number and anticipated record number are shown in the title-bar of the record window but there is, of course, no key as yet because there is no data. The title-bar therefore reads (New) in the place where the key normally appears. In most databases you may leave blank any fields except the primary key field. Powerbase does, however, allow other fields to be defined as mandatory fields . If the database contains such fields (which normally have red as their foreground colour) you will be told about them when you try to move to another record or close the database and will be unable to do so until you fill them in. You might also find that on some fields pressing certain keys has no effect. This will happen, for example, if you try to type letters into a field which has been defined as Numeric. If you type into a field whose text and border are dark green you may get an error message. These fields only accept certain values which are listed in a validation table (see ). To see what these values are make sure the caret is in the relevant field then click on the List values button on the keypad After entering data into a field you can go to the next field in sequence by typing either the Return or the down-arrow key . To go back one field use the up-arrow key . These arrow keys work in such a way that attempting to go beyond the last field of the record takes you to the first field and attempting to go back from the first field takes you to the last. Typing Return on the last field of a record acts rather differently in that it writes the record to disc and brings up a new blank record for editing, i.e. it duplicates the action of the Add record button on the keypad . You can, in addition, place the caret in any editable field by clicking with SELECT. Arrow keys, action when editing When you have entered as much of a record as you wish you can click Add record again for another blank record. The record you have just entered is automatically written to the database. The same is true if you click on any other keypad button or close the database altogether. You don t need to use the Force update ) button to tell Powerbase to write the record. The real purpose of this button is to make Powerbase save indices and validation table s which are stored in memory. Occasional use of the button during a long working session guards against power cuts or system failure. 2.4.2 Deleting records The fourth row of the keypad has only three buttons, the middle one of which suggests putting an index card in a dustbin. This is obviously the Delete button Ctrl F10 ) but what are the others? The left button ( ) moves the displayed record back one subfile, i.e. if the record is in subfile 1 it will be moved to subfile 0. If it is in subfile 0 it will be moved into subfile 5 ( wrap-around again). The right button ( ) moves the record forward one subfile. A record in subfile 5 will be moved into subfile 0. These buttons, which we will refer to as Shift back and Shift forward respectively, clearly don t delete records at all, except in the sense delete from the current subfile . We often want to get a record out of our way but keep open the option of bringing it back again and that s where these buttons are really useful. If you have a lot of records in subfile 0 and want to do a clear-out you can use subfile 1 as a dumping-ground for the unwanted records. You re not really deleting them: just removing them from the subfile you re working in. It is sometimes useful when shifting a record, to be able to follow it into its new subfile. You can do so by holding down SHIFT while you click on the Shift forward Shift back button Shift forward button Delete (dustbin) button should only be used when you are sure you want to lose the record permanently. As this is quite a drastic action Powerbase will ask you to confirm it before the record actually disappears. Nervous users may prefer to leave this button strictly alone and do all their deleting with Shift forward . If this eventually results in a lot of clutter in the subfile next to the one you re using you can always do an occasional clear-out as described in 2.5.7 2.4.3 Altering existing records A displayed record may be altered manually without any restrictions other than those applying to the addition of new records (see 2.4.1 ). Alterations which would result in a change to the primary key must be confirmed. Normal RISC OS conventions for editing writable icons apply, e.g. Ctrl U clears the icon. As with new record entries you need do nothing to save your changes to disc. The process takes place automatically before a different record is displayed, when a database is closed or on quitting Powerbase 2.4.4 Undoing mistakes Changes to the record on-screen can be undone provided you haven t clicked on Force update (not normally needed anyway) or done anything to cause Powerbase to display a different record. In other words, you can changes made to the current record as long as it remains on the screen. You may either the alterations made to the record or only those relating to specific fields. Undo facilities If you bring up the main menu and choose changes the record will be returned to the state it was in when you displayed it and before you began to edit it. To restore an individual field, click MENU over the field then go to the Field submenu and choose changes from there. Only ordinary writable fields and check boxes may be restored; you can t restore External field s (see ) which is why a warning is normally issued before deleting such fields. Editing the database special features Section described all the editing facilities you actually need in order to maintain your database. The features described in this section are not essential but they can save you time and make your use of the database more efficient. 2.5.1 Using a template Template, use in data-entry You may want certain fields to be filled in with fixed contents on every new record to save typing the same thing every time and Powerbase provides a means of doing this. Call up the main menu, go to the Miscellaneous submenu and choose Edit template . A blank record is displayed and the window title says Enter data which you wish to appear by default on new records . Type in the required fixed data then click any of the usual keypad buttons. You will now find that the new record displayed every time you click Add record is no longer blank but already contains the data entered in your template. This feature affects records only; it doesn t have a retrospective effect on existing records. fixed data on new records 2.5.2 Copying fields Fields, copying The buttons at the far left and right of the third row of the keypad may be thought of as Copy to Clipboard Ctrl C ) and Paste from Clipboard Ctrl V ) respectively. They can save a lot of time when you want to copy data from one field into another. When you click SELECT on the contents of the field which owns the caret is stored in memory. Positioning the caret in another field and clicking on Paste will copy the stored data into the new field, erasing the field s previous contents (if any). If the data is too long for the field it will be truncated. If there is nothing on the clipboard the corresponding data from the previously-displayed record will be copied to the field. Copy Paste are not limited to copying into another field of the same record; you may use them to copy data between records or from a record into a validation table or a dialogue box or, indeed, between any two writable icons within Powerbase . Data remains on the clipboard until you use Copy again. If you have highlighted fields using ADJUST as if for printing (see ) clicking SELECT on the Add record button displays the new record with all the highlighted data from the previously-displayed record already entered. To prevent the accidental creation of unwanted records primary key fields are copied in this way. (Remember that a record will not be added to the database if the primary key field is blank.) This is a suitable place to explain how Powerbase can also copy data to and from other applications by means of the global clipboard. The latter is a feature of RISC OS which few users seem to know about and not all applications use it. Here s how it works. Highlight one or more fields with ADJUST as described above and make sure the caret is in the displayed record. It needn t be in one of the highlighted fields; any field will do. Now type Ctrl Shift C . Nothing appears to have happened but the highlighted data is now on the global clipboard. If you open, say, an Impression window, place the caret in it and type Ctrl V the data will be copied to Impression Global clipboard, support for If, on the other hand, you highlight data in Impression and type Ctrl C , then place the caret in a Powerbase record and type Ctrl Shift V the data will be copied from Impression Powerbase . The same method may be used to copy data from Powerbase Writer, Easywriter Techwriter. StrongEd you need to type Ctrl Shift V . Although copying in the reverse direction works fine with Impression it does not appear to work with these other applications. doesn t appear to support the global clipboard at all. 2.5.3 Copying an entire record Record, copying entire When a new record contains much of the same information as an existing one you can save time by copying a whole record and editing the copy. Display the record you want to copy and then click the Add record button with ADJUST instead of SELECT ( Shift F10 ). No blank record appears when you do this; instead an exact copy of the current record is displayed and a message appears informing you of this. If you make no changes to the copy Powerbase will add the new record to the database. (It is assumed that no-one will ever want two absolutely identical records in the same database!) Any alteration, however, leads to the record becoming part of the database. 2.5.4 Choosing the field where editing starts Editing, choosing starting field When an existing or blank record is displayed Powerbase places the caret in the first (i.e. lowest-numbered) writable field of the record. This might not necessarily look like the first if the fields have been placed in out-of-sequence positions in the window. When going through a database making changes to one or two fields somewhere in the middle of records it might be convenient to have the caret start somewhere else. Click MENU over the field where you wish the caret to start, go to the Field submenu and choose Start editing . Whenever a record is displayed the caret will be placed in the chosen field. The same procedure is used to restore the normal starting position. 2.5.5 Changing many records at once To alter a given field in a set of records point the mouse at the field and click MENU. Go to the Field submenu and choose Global changes . In the first writable icon ( Replace ) enter what you want changing and in the second ( ) enter what you want the field changing to. In the third you may enter a search formula (see ) specifying which records are to be changed. Finally click on Change or type Return. Note the following: Search and replace Only records in the current subfile are affected. Replacement only occurs if the new data will fit in the field. Leaving Replace blank causes the target field to be replaced in any record which matches the search formula , otherwise replacement occurs only where the existing field matches the Replace string. Leaving blank causes the target field to be blanked in records which match the search formula and in which the target field matches the Replace string. Leaving the search formula blank causes replacement to occur in all records of the current subfile for which the target field matches the Replace string. It should be apparent from the above that leaving both Replace Search formula blank will place the string in all records of the current subfile. Since this is a rather drastic facility you will be asked for confirmation before the changes go ahead. The characters and may be used as wild-card characters in the Replace and strings. The first of these is used to represent a group of characters which don t need to be matched. If you enter: Replace $Street with $Road the substitution will be performed on any record which matches the search formula and where the target field with Street . The ending will be changed to leaving the rest of the string unchanged. Another example of use is: Wild cards in search-and-replace Replace with 1047$ If the target field begins with , the beginning will be changed to , the remainder left unaltered. Replace $MPUT$ with $NFUS$ would change occurring anywhere in the target field into , e.g. COMPUTER would become CONFUSER (!). You may, of course, use in the middle of a word: Replace re$ment with dis$d would cause a string in the target field beginning with and ending with to have its head and tail replaced with and , leaving the middle unchanged. e.g. replacement would become displaced character is used to represent a single wild-card character and can replace individual letters in specific positions. The Replace with strings must be the same length and should have characters in the same positions. The characters in the remaining positions are the ones which get replaced, but only if they occur in the corresponding positions of the existing field value. You may also use Global changes to perform simple calculations e.g. will add 12 to the current value in the field on all the matching records. *5-13 will multiply the current value by 5 and then subtract 13. It is operations like these which are most likely to produce values which won t fit in the field, especially if floating-point numbers are involved. The operation isn t limited to Numeric field s, or even to other types of field consisting wholly of numerals; Powerbase will find the number embedded in an entry such as ABC45XYZ and the operation would convert it to ABC57XYZ arithmetic in search and replace 2.5.6 Moving or deleting many records at once Choose Move/delete from the Miscellaneous submenu. This opens a window which gives you the choice of three types of operation: Deleting a batch of records Moving a batch of records Batch delete/move Delete records from a designated subfile accumulating records in one subfile Accumulate records from all subfiles into one designated subfile records from one designated subfile to another The default is to Move records from the current subfile to the next in sequence, but any source or destination subfile may be specified by clicking on the bump-icons. Type in a search formula (see ) and click the default action button or type Return. All matching records will be moved or deleted as specified. Operations by clicking on the button, but only for the last operation carried out. 2.5.7 Hiding sensitive data Fields, making invisible and inaccessible Invisible fields Your database might contain information which you don t want just anyone to read. A field may be hidden by placing the caret in it and typing Ctrl Shift F9 . The text changes from black to mid-grey to tell you that something has been done to it. If you subsequently type Ctrl Shift F2 all such fields will vanish. You can make them reappear (in mid grey) with Ctrl Shift F1 but they will always be invisible when the database is first opened. Ctrl Shift F9 has a toggle action: repeating the operation on a field removes the protection. Whilst in the invisible state a field cannot be selected for printing (see ). ADJUST has no effect and you can t sneak round the protection by using Ctrl A to select all fields or by choosing a range containing the hidden field (i.e. by clicking with SELECT on an earlier field then double-clicking with ADJUST on a later one). Its tag doesn t appear in the Field entry of the main menu when you click MENU in the relevant place, nor does the field appear in the field list produced by typing Ctrl F . As soon as you reveal the data by typing Ctrl Shift F1 it can be printed as normal. If you intend to use this facility you are strongly urged to password-protect your database (see Ch 11 ). Only a user with Manager access rights will then be able to use Ctrl Shift F1, F2 and . If you leave the database unprotected then anyone can use the key combinations and might easily discover them by chance (or by reading this manual!). External field The data in External field s is not stored within the Database but in separate files, one for each record. With the exception of Remote fields these files are stored inside the database s application directory. They may be Text, Sprite or Draw files and can be linked to buttons on the record window. Clicking on these buttons loads the External file into Paint provided that the relevant program has been by the filer. (For Remote fields the file objects can be of any type whatsoever including applications and directories and clicking on the button performs the appropriate action, e.g. an application is run, a directory is opened, an Obey or Basic file is executed, other files are loaded into the appropriate editor if the filer knows where it is. Remote fields are discussed fully in It is also possible to link Text files to a Text block field which will actually display the text (with certain limitations) within a large icon. Sprites may be similarly displayed in a Picture field. 2.6.1 Linking files to the fields External fields, linking files to To link a file with an External field drag the file s icon onto the record window and drop it on the appropriate field. For example, if the record includes a field represented by a Text button , i.e. one bearing a small version of the Edit icon, you may drop a Text file onto it. The button s icon changes to the small version of the Text file icon to indicate that the file has been linked. Sprite and Draw files are linked in the same way and Powerbase will not allow the wrong type of file to be linked. A Text block field also may have a Text file dropped onto it. The text (or as much of it as will fit inside the icon) is then displayed on the record window . The formatting will not be the same as it is when you examine the text in ; lines are word-wrapped and centred within the display icon. (This is a feature of multi-line RISC OS text icons and is out of the control of Powerbase .) You can t edit the text directly but it is possible to export the file for editing (see below). Sprite files may be dropped onto and displayed in a Picture field. Any new file dropped onto an External field which is already linked to a file simply replaces the old file with the new. 2.6.2 Editing External fields You can t edit an External field directly, only via an appropriate editing program such as Paint . As long as the editor has been by the filer clicking on the on-screen button with SELECT will load the file into the editor and display it. You can edit the file and re-save it without altering its name and it will remain associated with the same field of the record. Text block and Picture files are loaded into their editors by a double click. External fields, editing 2.6.3 Clearing and exporting field contents External fields, unlinking External fields, exporting Clicking MENU over an External field makes available certain entries on the Field submenu which are shaded for other types of field. Remove external lets you delete the linked file from the database application. As deleting a file is rather drastic you would probably like to be warned when this is about to happen so the message Delete object? Are you sure? normally appears. Finally, Save contents on the Field submenu lets you export the linked field to a filer window or to another application. 2.6.4 Editing scrollable lists scrollable lists, editing Entering or altering data in a scrollable list is not very different from doing so in other types of field, but some keys do behave differently and there are extra keystrokes to access the special features of such lists. Return moves the caret from cell to cell across the current row, then jumps to the start of the next row. When it reaches the last visible cell the list will scroll up a row if there are still rows to display. If the last cell of the list has been reached the caret moves to the next field or next record, just as it does for other types of editable field. Shift-Return behaves like Return until the last cell of the list is reached. It then adds a row to the list, moving the caret to the start of the new row. You may add as many rows as you wish and each record can have a different number of rows. Insert with the caret anywhere in the list also adds a row to the end, but this time without scrolling the list or moving the caret. There is (at present) no way of inserting a blank row into the middle of the list. Shift-Insert is used to remove unwanted blank rows. (Not Shift-Delete, as you might expect because this will have the same effect on characters as Delete!) This key-combination is quite safe: it will only delete when the caret is in the last row and only if that row is blank. Ctrl-Shift-Insert will force the deletion of whatever row the caret is in. You aren t very likely to use this accidentally. scrollable list, deleting rows scrollable list, adding rows Neither Shift-Insert nor Ctrl-Shift-Insert will let you delete rows so that the number is fewer than the window can display: when the scroll-bar completely fills its slot this minimum has been reached. Up and Down arrows move the caret vertically from cell to cell, staying in the same column. When at the top or bottom of the list wrap-around occurs instead of the caret moving to the previous or following field as it does for other field types. Wiping a scrollable list scrollable list, clearing Click MENU over the list and go to the Field submenu. Choose Blank list . You will be asked for confirmation before the data is deleted. Data export and import scrollable list, exporting from scrollable list, importing to When MENU is clicked over a scrollable list two choices become available on the Field submenu in addition to Blank list noted above: Save as list saves the contents of the scrollable list as a plain text file. The contents of each cell of the list occupies a separate line and there is nothing in the file to indicate which items came from the same row and which came from different rows. If such a list is dropped onto a scrollable list field the data will be imported cell by cell, working along each row to the end and then moving to the next. Save as CSV saves the list as a CSV file (see ) with each row of the list as a separate CSV record and each cell in the row as a CSV field. The characteristics of the file are determined by the CSV Options window just as they are for exporting data from ordinary fields. If you drop such as CSV file onto a scrollable list field the result is somewhat different from the importing of a text list as described above because the data in the file is more structured; the row and column information of the field from which the data came has been retained. Suppose you have exported such a file from a 3-column scrollable list. If you drop the file onto a 2-column list the third item in each line of the file will be ignored: the destination field will contain what the first two columns of the source field contained. If, on the other hand, you drop the file onto a 4-column scrollable list the 4th column of the list will be left blank. nd and Colours referred to above which are used to identify key fields are the default colours but it is possible for the user to change them (see * If the validation feature proves to be a nuisance you can turn it off. To do so bring up Powerbase iconbar menu and choose Preferences . One of the option switches on the left of the preferences window is called Validate input . Turn this switch off and click on Accept . All validation, including the insistence on filling in mandatory fields, will then be disabled. If the primary key is derived from two or more fields you need not fill in all of them as long as the result is not a null key. The characters used to represent single and multiple wild-card characters may be changed via the Preferences window (see 14.5.2 * If you drop a text file in the wrong place in the record window Powerbase will think you want to treat the file as CSV file and import data from it (see CSV files ) and the Import text file window will appear. This is rather startling but harmless enough as long as you don t click on Import ! Click on Cancel to get rid of the window. Unlinking objects attached to Remote fields, Run file buttons and Directory buttons is also done like this (see 4.2.8 and 4.2.11 Ch 3 Printing from the Database This chapter describes how to print out selected data from a database, including the printing of individual records and of labels. Such a print-out is commonly referred to as a report Output destination Printed output may be displayed in a window, directed to a specified text file or sent straight to the printer. Choosing Options from the Print submenu (keystroke equivalent Ctrl Print ) displays the Print Options window and you will see at the top of the window three radio buttons, Window and Printer , which allow you to select the output destination . Each of these options will now be described. 3.1.1 Window destination Saving from a Report window Printing from a Report window This is the default setting and, as its name suggests, it displays the completed report in its own window. Clicking with MENU over this window opens the Report menu which offers four choices: Save as text leads to a Save box from which an icon may be dragged to a filer window or to any application which can accept a Text file. The supplied pathname uses an appropriate leaf-name for the file and points to a directory called PrintJobs which is inside the database directory. Each database has its own PrintJobs directory whose contents can be displayed by choosing Show jobs done ) from the Print submenu. To save files with the least bother simply click on or type Return. You can, of course, delete the pathname ( Ctrl U ), type in your own filename and drag the file icon to any open directory. You might also be able to produce hard copy by saving the text to the Printers icon on the iconbar, although some recent printers contain no fonts of their own and therefore cannot print text files in this way. Look in PrintJobs regularly and get rid of files which you no longer need. Sorting lines in a Report window will sort the report on whatever column the mouse pointer was over when you clicked MENU. Shrink list will remove as much surplus white space as possible from between the columns of a report. This item might be shaded: white-space removal can be set to occur automatically before the report is displayed (in fact this is the default setting). If this is the case no further space can be removed and the menu choice is therefore made unavailable. PrintJobs directory Scrap removes the report window from the screen and recovers the memory it occupied. You might find this useful if you run short of memory after creating a large report. Calling up records from the report window Records, retrieving via Report window The report window has one more useful feature. If you point at a particular piece of displayed data and double-click with SELECT the record window will come to the front displaying the relevant record. If the field corresponding to the item you clicked is editable the caret will be placed in that field. If you are using a report to look for errors in the data you can quickly correct them by this method but the report won t change to reflect your corrections until you re-create it. If you double-click with ADJUST instead of SELECT the record window opens at the pointer and its size and scrolling are adjusted to show only the required field. 3.1.2 File destination Save box will appear as soon as you tell Powerbase to go ahead and generate the report. The supplied pathname is the same as would be displayed when saving from the report window. Simply click to save it in PrintJobs under that name. Alternatively, type a filename and drag the icon to an open directory. The report will then be created and the file closed. Nothing else appears on the screen in this case. The File destination is of greatest use when reports are being produced from a Script file (see Ch 12 3.1.3 Printer destination When the Printer radio button is selected the button alongside becomes available. (It is shaded when Window is selected.) Clicking opens the Printer Setup window which provides a wide range of options. You may specify the number of copies to print, choose the font and point-size to be used, print with the paper upright (portrait) or sideways (landscape) and set the inter-line spacing and any or all of the four margins. All measurements other than font size may be specified in mm (default), inches or points. Print formats formats for reports reports, formats for Powerbase is capable of producing reports in four different formats. Only two of these, Horizontal and Vertical , are available when you print to a window or a file and these are selected via two radio buttons on the Print Options window. When the output destination is Printer two additional formats, Table and Label , are also available and are selected from the Printer Setup window.These four formats will now be described. 3.2.1 Horizontal Headings in reports numeric fields, justification of This is so-called because each record appears on a single, horizontal line with the fields aligned so that they form neat columns. Non-numeric fields are left-justified. Numeric fields, and others whose content is treated as a numeric value (see 3.5.2 ), are right-justified. A header line identifies the columns by means of the tags or descriptors of the fields and this header can be made to appear on every page of the report or on the first page only. An optional descriptive title may also be incorporated. The Spacer is used to separate columns (see ). All these features (and others) are chosen from the Print Options window. Besides the ability to sort a report in a window (see 3.1.1 ) you may force Powerbase to sort the report before displaying it. To do so, select the Sort on option button in the Print Options window and enter in the associated writable icon either the tag of the field on which you wish to sort or the column number of the report. The feature is only of use in Horizontal and Table formats, but may be used with the Printer destination as well as with Window sorting, specifying before printing 3.2.2 Vertical Fields are printed underneath each other with the identifier (tag or descriptor ) at the left hand side. Where the field selection includes an external text file, i.e a Text or Text block field, the Vertical format is the only one which may be used and will be selected automatically. Although each field normally occupies a line to itself you can override this by holding down Shift as you click with ADJUST to select the field. You will then get a new line after the field: the next field to be selected will appear (with its identifier) on the same line. We will call this function field concatenation It may be applied to any number of fields: keep Shift down while selecting all except the last one to appear on the line. This feature is very useful if your report contains a mixture of long fields which need a line each and short ones which don t and would otherwise result in wasted space and paper. Concatenated fields are separated by the Spacer and the width of the report is governed by Text width (see 3.2.3 Table Line spacing, for tables This is only available when outputting to the printer. It resembles Horizontal format but the lines and columns are separated by horizontal and vertical rules, forming a grid. When this format is selected a number of extra features are enabled allowing you to include extra (blank) columns and lines, making this format especially useful when you want a list to which information is to be added by hand (e.g. entering marks against a printed list of students). The number and width of blank columns and the number of extra lines may be specified. For a tidy result it is recommended that you increase the line-spacing from the default 120% to about 150% when using Table format. 3.2.4 Label This is meant for printing on special label stationery. Since such stationery is expensive you are advised to try out your settings on plain paper first! Selecting this format enables the label setup choices which include the label size and the number of labels in a row. It also allows optional fixed starting and finishing lines to appear on each label. The number of lines on the label is not needed: Powerbase works this out from the label height and print size. Labels, number of lines printed Printing will normally begin on the first label in the first row on the sheet but, to enable you to use up a part sheet of labels, you may specify which label to begin with, e.g. for three-in-a-row labels, entering 5 would make printing start at the second label of the second row. After the first sheet the starting-point reverts to the first label in the first row. Labels, using up part sheets Labels, including primary key Each field normally appears on a separate line but fields may be concatenated in the same way as for Vertical format, with the Spacer being used to separate the fields ( see 3.10 ). This may be necessary if you are using separate fields for surname and initials or surname and forename. Labels, substituting for blank field You may specify one field to be substituted for another if the latter is blank. Both fields are specified by tag and the Substitute switch is set. This is useful in a school or college situation where labels are being addressed to parents. Mature students, for whom the parent field in the record is blank, can have their own names printed instead. Another switch makes the primary key of the record appear in small print on each label as a means of identification. This can be useful if the data printed on the label doesn t make it obvious which record it comes from. What types of field can be printed? printable fields Powerbase can use many different types of field. All are described in 4.2.5 in connection with setting up a new database and you should refer to that section to clarify what follows here. You can print data from the following types of field: Editable field (i.e.one into which you can type directly), including Scrollable lists Computed and Stamp fields. Text and Text block fields. Check Boxes . What is printed for these differs from what you see in the check box. Thus: (a) Tick/Cross boxes result in (b) Tick/Blank boxes result in Check boxes, appearance in reports (c) Star/Blank boxes result in an asterisk or (d) Null/Tick/Cross boxes result in Check boxes, 3-state (e) ?/Tick/Cross boxes result in Note that (d) and (e) are three-state check-boxes; (a), (b) and (c) are two-state check-boxes. It is, of course, possible to print Draw and Sprite fields from individual records by loading the external file into Draw Paint and printing from that application (See 2.6.2 Specifying which fields to print The field or fields selected for printing is called a print selection. Point at each of the required fields and click with ADJUST. The fields will be highlighted by reversing the foreground and background colours. Only those fields which are printable (see ) will respond to ADJUST in this way. A second click will de-select the field. Note that the order in which you select the fields is important since that is the order in which they will appear in the report. The menu button at the bottom-centre of the Match window (see ) will list the fields in the order in which they have been selected. Ctrl F has the same effect. (If no fields are selected Ctrl F gives a listing of the fields.) choosing fields to print A contiguous range of fields may be selected by placing the caret in the first field then double-clicking with ADJUST in the last. To select printable fields choose Select all (Ctrl A) from the Print submenu. There is also a Clear selection entry on this submenu ( Ctrl Z 3.4.1 Saving print selection files PrintRes directory Print selections may be saved for future use. Save selection from the Print submenu leads to a Save box. Accepting the default pathname will save the file with the name Selection in a directory called PrintRes . Just as every database has its PrintJobs directory, so does it also have its own PrintRes (i.e. Print Resources ) directory whose contents can be displayed with Show resources (Ctrl R) from the Print submenu. Selection files are of type &7f3 and are distinguished by their icon which bears a large . Because they have a specific file-type which Powerbase recognizes they can be loaded by double-clicking on them. 3.4.2 Default selection You may save as many print selections as you like and use any file-names, but one name is special: a file saved as !Selection is treated as a default selection . When you instruct Powerbase to create a report without having first highlighted the fields to be included, the default selection will be searched for in PrintRes and used. If there is no such file the primary key field(s) will be printed. As soon as the report is complete the selection is cleared. You won t see the highlighting of the fields at all when a selection is used automatically in this way. To save a default selection you need only select the option button Default selection in the Save box and accept the supplied pathname by clicking or typing Return. Specifying which records to print Unless we want to print all the records in the database we need some means of telling Powerbase what are the common features of the records we wish to print. There are two ways of doing this. The more versatile way (and the one which Powerbase uses by default) makes use of a search formula query describing the characteristics of the required records. The remainder of this section deals with the construction and use of search formulae. For the alternative method, query by example see Section If you choose Print from the main menu a small window with the title Find matching records appears. We will call this the Match window . The same thing happens if you go to the Print submenu and choose Create report . Simplest of all is to just type the Print key on the keyboard. The most prominent feature of the Match window is a group of icons enclosed by a thin red border. This object is called the Query panel and you may have already seen it since it forms part of several windows. It appears when you select the Filter switch on the keypad for example (see 2.3.3 ) and is also used when making Global changes 2.5.5 ), performing a Move/delete operation on a batch of records ( 2.5.6 ), doing a Mail merge 9.2.2 ), exporting a CSV file ) and creating a Subset The writable icon in the Query panel , in whatever context the latter appears, is meant to take a search formula . The simplest thing you can do, of course, is to type nothing at all! If you then click on the Print button you will create a list of all the records in the current subfile of the database. You could achieve the same result by typing , indeed if after producing the above list with a null formula you click on the button Ctrl O) , which retrieves the last-used search formula , you will find ALL displayed. 3.5.1 The construction of search formula Most database queries will involve a selected group of records. A search formula describes the criteria which records must fulfil in order to be included in the report. Getting to grips with search formula e is, perhaps, the biggest hurdle faced by the new Powerbase user and you are referred first to the simple examples described in the Tutorial file. A search formula consists of one or more search element search element specifies that a field value must fit a certain condition and takes the form: <TAG LIST>< COMPARATOR ><TARGET LIST> The angle brackets are there for clarity and are not used in entering the actual formula. There must be no spaces between the three parts. A tag list (if it contains more than a single tag) has the form: tag1,tag2,tag3,.... where tag1 etc. are field (see 4.2.1 and 4.2.4 ) which identify the fields to be matched. A target list (if it contains more than a single target) has the form:- target1,target2,target3,.... If the Case switch on the Query panel is selected then all comparisons will be case-specific, e.g. will be regarded as different from . If the Case switch is not selected all those three will be considered identical. Fields are normally listed in ascending alphabetical order or, for numeric fields, ascending numerical order. If the Reverse switch on the Query panel is set they will appear in reverse order The heading of a report shows which fields were used in the search formula and what targets were specified. If a target was placed in quotes (which is the only way of searching for any string containing a comma, for example) then it appears in quotes in the heading also. It is impossible to describe the use of search formula e adequately without quoting actual examples. As in the Tutorial file we will mainly make use of the Elements sample database. A simple example of a search formula consisting of a single search element is: where GP is the field tag, = is the comparator and T is the target. This means The field whose tag is GP must contain the value T , i.e. all transition elements (but no others) are to be included in the report. A slightly more complex one is: GP=1,2,3 which would be interpreted as The GP field must match one of 1,2 or 3 . This may also be entered as: GP=1 OR GP=2 OR GP=3 which is possibly easier to understand but also somewhat longer. A further example is: OR in search formulae OX1,OX2,OX3=3 meaning One of the first three oxidation state fields must have the value 3 This could also be entered as: OX1=3 OR OX2=3 OR OX3=3 Yet another way is: OX1-OX3=3 i.e. you may specify a range of adjacent fields by giving the first and last separated by a hyphen. Where you don t know which fields to test you can replace the tag, tag list or tag range with @, which causes all the fields in the record to be examined. hyphen in search formulae Note that in these examples only of the fields in the tag list is required to match of the targets in the target list (although it doesn t matter if more than one field matches more than one target). Sometimes we want an inclusive search so that of the fields in the tag list match a given target or, less frequently, a field contains of the values in the target list. It s a matter of connecting the search elements with AND instead of OR. You can do exactly that: OX1=3 AND OX2=3 AND OX3=3 AND in search formulae You may also save typing by using the ampersand (&) instead of the word AND, but the same result can be achieved even more briefly by simply doubling the comparator , in other words using instead of so that the formula becomes: OX1,OX2,OX3==3 The full list of available comparator s is: Comparators in search formulae (list) doubled comparators in search formulae =, <>, <, >, <=, >=, { and (N.B. may be used instead of should be read as contains and as does not contain . These are used where the target value must (or must not) be part of the field but isn t expected to make up the whole field. The use of { is the main source of those rare instances where we want the items in a target list to be matched in a given field. e.g. Suppose we knew that someone s house number was 17 and that they lived on <something> Avenue but the actual name couldn t be remembered. In a database of addresses a search formula such as: ADDR{{17,Avenue (note the doubled comparator) would find it, whereas: ADDR{17,Avenue would find all those addresses where the house number was 17, regardless of street name, and all those addresses with Avenue in them, whatever the house number. You may invert the logic of a search criterion by putting NOT in front of it. To print all non-transition elements you could use: NOT in search formulae NOT (GP=T) Note the space after , the need for brackets, and that the syntax GP NOT=T . You could equally well use one of the following: GP<>T GP~T and may find either of these more understandable. To make multi-criterion searches either place and targets in comma-separated lists as described above or string search elements together with the connectives AND and OR. Use AND (or the ampersand, &) when a field must meet of a set of criteria. e.g. GP=T & Z>50 & NAME{IUM for all transition metals with atomic numbers greater than and names containing . Use when a field need meet only of a set of criteria. e.g. GP=L OR GP=A would find all lanthanide and actinide elements as the formula means either L or A ; I don t care which . AND and OR can produce ambiguous search formulae e.g. GP=1 OR GP=2 AND Z<50 could mean either elements in group 1 or 2 (don t care which) with atomic numbers less than 50 or group 1 elements (of any atomic number) or group 2 elements whose atomic numbers are less than 50 . You probably want the former, but Powerbase will give you the latter. To get what you require use brackets to make the logic clear. In other words write it as: OR in search formulae (GP=1 OR GP=2) AND Z<50 You could also write this as: GP=1,2 & Z<50 without the need for brackets at all. 3.5.2 Numeric and other special fields in search formulae For most types of field the comparison with the target is made by character matching but for certain types the comparison uses the numeric value of the field. The fields concerned are: Numeric, Calculated, Record number, Sequence number, Day of month, Month number, Year Only the first of these is an editable field type but all may be included in search formulae. Suppose we have a Numeric field whose tag is NUM. If you entered the formula: Numeric fields in search formulae NUM=5 the record would be included in the report if NUM contained 5, 05, 5.0 etc. because all of these have the same numeric value. If you had an Alphanumeric field called NUM the same search formula would only match records where the content was literally 5, i.e. the character . This can easily catch you out. Suppose, for example, you want to print records for which NUM<8. You might be surprised to find records in which NUM contains values such as 55, 20, or 13 being printed, as well as those containing 4, 6, 2 etc! If this happens check what type of field NUM is. Unrestricted and Alphanumeric fields will give the above result; Numeric fields (and the others listed above) will give the result you probably want. You can force comparison by numeric value for a field which consists of (or, at least, begins with) numerals, even though the field is not defined as of Numeric type, by enclosing the field tag in square brackets, e.g. [NUM]<8 would produce the desired result in the above example even if the field is Alphanumeric or Unrestricted. This is useful where you want to make a comparison but still allow the field to accept non-numeric characters. The comparison-by-value can only work in such cases if the number part of the field comes first. e.g. it will deal correctly with 55A, 20B, 13X but not with A55, B20, X13. 3.5.3 Using wild-cards search formula The use of characters and wild-cards was described in 2.5.5 in connection with search-and-replace operations. They may be also be used in search formula is used to represent a group of characters and to represent single characters which do not need to be matched. e.g. If (still using the Elements database) you type: Wild cards in search formulae NAME=$ON you are, in effect, saying find all the elements whose names end in ON ; I don t care what precedes ON as long as nothing follows it Powerbase will duly find CARBON, BORON, NEON etc. If you were to use: NAME=$ON$ You would find PLUTONIUM, POLONIUM but CARBON, BORON, NEON; something precede ON as well as follow it. (To print both sets of elements you would use NAME{ON Note also that: NAME=$TIN$ finds PROTOACTINIUM, PLATINUM etc, but TIN itself. NAME=S$IUM finds all names which begin with and end with , e.g. SAMARIUM, SCANDIUM and SODIUM. The effect of: NAME=S####IUM is somewhat different. You are, again, asking for names which begin with S and end with IUM but this time SAMARIUM and SCANDIUM would be found, but SODIUM since you have specified exactly 4 wild-card ed letters between the S and the I. Finally, to find any 5-letter name, regardless of the actual letters: NAME=##### 3.5.4 Comparing the contents of two fields A field tag (instead of a literal string) may be specified as a target, thus allowing two fields in a record to be compared to produce, for example, a list of all records in which the relevant fields have the same content. This might interfere with a normal query where the required literal target happens to be the same as the tag of another field. The problem can be overcome by enclosing the literal string in quotes. Query file, default 3.5.5 Saving search formulae for re-use Choosing Save query from the Print submenu opens a Save box from which the search formula may be saved. By default the file is saved in PrintRes under the name Query . Selecting the Save as default switch on the Save box will cause the file to be saved as the default query with the name !Query . If such a file exists in PrintRes it will be automatically entered in the Query panel whenever the Match window is opened. A default query file, in other words, behaves in a similar way to a default selection file as described in 3.4.2 . Query files are of type &7f4 and are recognizable by the large in their icon. You may save as many Query files as you like and load them into the query panel by double-clicking on them. query files Query by example After that lengthy description of the search formula method of querying the database we turn to the alternative: query by example. For brevity when comparing the two we will refer to them as and respectively. Select the option switch at the top left of the Match window in order to use QBE. 3.6.1 What is The user is presented with a blank record and invited to type into the relevant fields the data which must be matched in order for the record to be included in the report. What you are saying in effect is: I want a list of all records which look like this. I don t care what s in any of the fields I haven t filled in, but the ones I filled in must correspond to what I have typed. e.g. in the Elements database if you wanted to print a list of all transition metals you would simply enter in the Group field and then proceed with the report. The of the field isn't needed at all, whereas using a search formula requires you to type . Enter the data to be matched then either click with SELECT on the Print button of the Match window or else press the Print key. If you simply enter the required target strings Powerbase assumes that you want the relevant fields to match exactly , i.e the effect is the same as using in a search formula. There are, however, other comparators besides which may be used in search formulae. (see 3.5.1 for explanation and complete list). You may use any of these in a QBE query by placing them at the start of the string, e.g. {Avenue in an Address field would match all records where the field contained the word Avenue . An address such as 15 Acacia Avenue could be found by this method whereas just entering the word Avenue wouldn t work because it would require the field to read Avenue and nothing more. Wildcards may be used; e.g. you could print from the Elements database all elements ending in IUM by entering in the field or all those whose symbol began with H by entering in the field. You may specify a target list (see 3.5.1 ) to make the search include all records matching any item in the list. e.g. Leeds,Liverpool,Manchester in a Town field (if it will fit) would cause records with any of these places to be included. You can also specify a field list (equivalent to a tag list ; see 3.5.1 ) provided that the fields form a contiguous group. The target string (which may be a target list, be wild-carded or be preceded by a comparator) is entered in the first field of the group. Press Return and enter " (double quote or ditto mark) in the next field and for the remaining fields of the group. (Pressing Return rather than moving the caret by means of the mouse ensures that you really are dealing with a contiguous group of fields.) 3.6.2 QBE vs SF So what are the advantages and disadvantages? QBE is very intuitive and avoids the need to bother with field tags or the minutiae of search formula syntax. On the other hand SF is more comprehensive and flexible: there are some things you simply cannot do with QBE. Some of the limitations have already been mentioned but here is a complete list. Query by example, limitations of Except where exact matches ( field=target string ) are required it might not be possible to fit the target string or list into the relevant field. One consequence is that you can't target a Date field for all dates prior to, say, 01-06-90 since dates fit their fields exactly leaving no room for the necessary < comparator. Such a search is perfectly possible with SF. Searches involving multiple fields and the same target, i.e. those where FieldX FieldY FieldZ must match are only possible where the fields form a contiguous sequence. With SF it is possible to perform such searches on fields dotted about the record. There is no equivalent in QBE to the @= ( field in the record matches) or @{ ( field contains) searches which are possible with SF. You can't search for text in an external Text or Text Block field. You can with SF. You can't include Check-box fields in QBE searches. Since QBE doesn't use tags you can t force a comparison-by-numeric-value on a non-numeric field (See 3.5.2 Calculated fields and those belonging to the Stamp class cannot be used by QBE because you cannot type into them! If your requirements involve only fairly simple searches then QBE might be just what you ve been looking for, but if complex multiple searches are often needed then SF is the one to go for. It is, of course, a simple matter to switch between the two. Help available within Powerbase As supplied Powerbase uses SF as the default query method and the Query by example switch will be deselected when the Match window is displayed. If you want to make QBE the default you can edit the relevant line of the Config file in !Powerbase.Resources to read Query instead of Query SF . Don t forget the space. Selecting the Print function will then produce the blank record without displaying the Match window at all. There is, of course, no Print button to click with the mouse after you have entered the target strings so you tell Powerbase proceed by typing the Print key after entering the data to be matched. Other features of the Query panel and Match window At the far left of the Match window is a group of four radio buttons labelled Print Count and Clear . Only one of these may be selected at a time and the default action button at the bottom right of the window reflects whichever one you select. When the Match window is opened it is always Print which is selected, this being the most often used feature. If you merely want to know how many records match a specified set of criteria, without printing them, select Count . The number of matching records appears to the left of the Cancel button (which merely closes the window). and Clear are explained in section 3.8.2 below. The icon to the left of Cancel indicates the selected output destination (see ) by displaying a representation of a window, a text-file icon, or a printer. In the latter case the icon will be shaded if no printer driver is loaded. Clicking with SELECT on the icon opens the Print Options window. , as stated earlier, retrieves the last search formula used. The action of the Case and Reverse switches has also been described (see 3.5.1) Print options window, displaying opens the Help window which offers another way of building search formula e which might appeal to beginners. Select the target field by cycling through the with the bump icons or by choosing from the pop-up menu. Choose the comparator by selecting a radio button. Type the target value into the writable icon. Place the caret in the Query panel writable icon and click Add to formula. The search element will appear at the caret. You may click on and enter other search elements in the same way. If you wish to use button you must do so before clicking Add to formula Powerbase inserts the brackets round the search element for you. Search formulae and Help window Holding down Ctrl and clicking on a field with SELECT while the caret is in the Query panel causes the tag of the field to be entered in the search formula at the caret. This, together with the above method of constructing search formula e, largely overcomes the problem (especially when using someone else s database) of not remembering what the field are. key, inclusion in reports record numbers in reports 3.7.1 Printing records from more than one subfile Reports are usually created from records in the currently-selected subfile which is displayed in the title bar of the record window. Just under the Query panel is the legend Incl.subfiles: and a row of numerals, 0-5. When a database is opened 0 will be highlighted, indicating that reports will only include records from subfile 0. If you change subfile by clicking on the appropriate keypad buttons you will see this highlighting move from one number to another, showing the selected subfile. You can, however, click on these numbers so that any or all of them are selected. Subfiles are deselected with a second click. subfiles, printing from several When you create a report from more than one subfile the records are not merged into one alphabetically (or numerically) ordered list; the ordering starts afresh for each selected subfile. This isn t really a problem because you can always sort the completed report on any field to produce a single, ordered list (see 3.1.1 3.7.2 Including record number, key and subfile number A group of three option switches in a frame to the left of the query panel allow you to include record numbers, the current key, and the subfile number in a report . The latter is especially useful if you have created a report from several subfiles and then sorted it as described above. If you need to keep track of which subfile a record comes create the report with selected. These three special items are not saved with a print selection file and always appear on a report before any of the selected fields. Marking records for inclusion or exclusion There are times when you want to print a number of records which have no obvious connection with one another: they may have a common feature which is obvious to but none within the records themselves. Such a situation commonly occurs when you want to print a few mailing labels. No common feature means no basis for constructing a search formula. So how do you tell Powerbase which records you want to print? 3.8.1 Single records A small panel attached to the bottom of the record window contains a check-box, Mark for printing etc. , which you can tick to indicate that the displayed record is to be printed. Using the Search button or the browse controls you can call up each record you want and tick the box. You then simply select the required fields and print in the usual way. If no search formula has been entered then only the marked records will be printed. (Printing without a search formula when no records are marked gives the whole subfile as described in ) If you do enter a search formula you will get the records which match the formula the marked records, whether the latter match the formula or not. printing arbitrary selection of records A pop-up menu allows you to invert the effect of this feature so that printing without a search formula gives all records in the subfile except the marked ones and printing with a search formula gives all the matching records except for those marked. When the menu option is set like this (to exclude rather than include) the check box shows a red cross instead of a green tick. marking records for inclusion including individual records Next to the check-box is the Clear marks button which does exactly what it says. It is shaded when no record is marked. A further indication of whether records are marked is provided by the small rectangle at the far right of the Query panel which is coloured green or red, as appropriate, when any record is marked. This applies to the whole database, by the way, not just to the current subfile. marking records for exclusion Powerbase takes heed of marked records in any operation which involves the query panel, i.e. batch move/delete, global change, filter, export subset, export CSV file, as well as print. excluding individual records 3.8.2 Groups of records It is sometimes useful to be able to mark (or clear marks from) a group of records which fit a search formula. This is made possible by the and Clear radio buttons on the Match window. By repeatedly selecting and executing different query operations you can build up a set of marked records by stages then, if desired, selectively clear the marks from certain ones. Finally, you can print your carefully-tailored selection of marked records without entering a search formula at all. Marking and unmarking records (search formula) Printing single records There are two methods of printing the displayed record only: (a) Hold down Shift whilst clicking with SELECT on the Print button of the Match window or type Shift Print on the keyboard. The highlighted fields of the displayed record are printed using the currently-selected print format as determined by the setting in the Print options window. If no fields are selected the action is as described in 3.4.2 Powerbase will use the default selection if it exists or, failing that, print the primary key fields only. displayed record, printing (b) Follow Export selected from the main menu to a Save box from which may be saved (or dragged to the printer or into a wordprocessor document) a text file containing the highlighted fields of the displayed record. If there are no fields selected the menu entry is shaded. displayed record, exporting Print options window Print options window, displaying To display this window you can choose Options from the Print submenu, type Ctrl Print , or click SELECT on the icon to the left of Cancel on the Match window. Features such as Destination (see ), Format (see ) and the Sort on facility (see 3.2.1 ) have already been dealt with extensively. The rest are covered here. Default settings appear in brackets after the name of the feature. Scrollable lists (as single row) These may be printed in two different ways. The default is for all the cells in the list to be made into a single row with semicolons separating the data from individual rows of the scroller. This can result in very long lines indeed. An option switch ( Shrink row - ON by default) causes as much white space as possible to be removed, but lines could still be too long for the printer. The alternative format puts the data from each row of the scroller on a separate line so that the data aligns in columns. This occupies less room horizontally but much more vertically. Experiment! scrollable lists, printing format Headings (tags) appear at the head of reports in all formats except Label unless is selected. Expand codes (OFF) causes extra data from a validation table to be substituted for (or added to) the coded data in fields linked to such tables (see Expand headers (ON) will show the expanded versions (see ) of the target values for fields linked to validation tables in the list header. Turning the option OFF causes the target values to be shown exactly as typed in the search formula. Upper case (OFF) causes all textual output to appear in capital letters. uppercase in reports Print header (ON) causes the printing of header lines at the beginning of each page. The header includes the following information: headers in reports footers in reports The name of the database plus a title, derived from the search formula , making it clear on what basis the records have been chosen. index used for the ordering, plus the date stamp (if appropriate switch selected). An optional description entered in the Title writable icon. Title on reports column headings as described above. The following switch (on p. 1 only: default OFF) limits the header to the first page of a report. Print footer (ON) Reports in Horizontal and Table format normally end with a footer which specifies the number of records printed. If the output includes Numeric field s and column calculations have been selected (see ) then the results of these too will be part of the footer White space in reports, removing Date stamp (ON) makes the date and time when the report was created appear as part of the header Shrink list (ON) Horizontal Table format the width of columns is determined by the maximum defined length of the fields included in the print selection. These lengths are often greater than the length of data actually present in the fields, resulting in a lot of white space between columns. With this option ON the surplus space will be automatically removed. Even if it is OFF you can still remove white space via the Report menu (see 3.1.1 ). Output to Printer always removes white space whether this switch is ON or OFF Page numbers (OFF) allows page numbers to appear at the bottom of each page of a report. This feature works quite independently of the Print footer switch. page numbers in reports Page length (0) determines the total length of page, including header footer and top margin, for destinations other than Printer (for which the page length is determined by the printer driver). The default value of 0 means no division into pages at all, but you might want to alter this if you drag text-files to the printer. An A4 page is 70 lines long, but you won t be able to print on them all and page-feeds might occur in the wrong place. Look at Edit paper sizes on the iconbar menu of Printers . Subtract the displayed top and bottom text margins from 70 and enter the value in place of 64, if necessary. In Vertical format Powerbase will try to avoid splitting a record between pages, but this can happen if the report includes Text or Text Block fields of greatly varying length. (It will also happen if the number of fields to be printed exceeds the length of the page!) text width in Vertical format page length Text width (A) specifies the line length used when printing in Vertical format. means and lets the program calculate the value. You may enter a value of your own (e.g. 70) to override this. Spacer (1) specifies how fields printed on the same line will be separated. Fields are first padded with spaces to the maximum width of the relevant data field (but see Shrink list above) and the spacer string is then printed before starting the next field. Three interpretations of the contents of this setting are possible: A number by itself means use the specified number of spaces. spacer in reports column spacer A number followed by a non-numeric character means use a string of the specified number of that character, e.g. means 3 hyphens. A non-numeric string is used as is , e.g. . If the former is used when outputting to Printer continuous vertical rules will be placed between columns in Horizontal format. 3.10.1 Saving print options files All the settings in the Print Options and Printer Setup windows may be saved as a Print Options file. Clicking Save choices with the in database radio button selected brings up the familiar Save box. By default the file is saved in PrintRes under the name PrintOpts . As with Selection (see 3.4.1 ) and Query files (see 3.5.5 ) you can save a default options file called !PrintOpts by selecting the Default options button on the Save box and Powerbase will load this whenever the database is opened. Options files have a large on their icon and a filetype of &7f5. You may save as many as you wish and load them with a double-click. If the in Powerbase radio button is selected the options are saved as the Powerbase default and no Save box is displayed. Load default reloads this file, overwriting any changed settings. print options files Field analysis reports Default Query file Analysing indexed fields Field submenu has an Analyse option which allows you to print a breakdown of the field contents under certain special circumstances: (a) If the field is index the menu entry will read Analyse index . When chosen it will produce a list of all the values in the index with the number of times each one occurs. e.g. A database of college students might have a field for the school of origin. If there are 20 different schools and if the field is index ed then a list of those schools will be generated showing how many students came from each school. (b) If the field contains an 8 or 10 character date (e.g. 19-10-42 or 19-10-1942) the menu entry reads Analyse months and a breakdown by month will be printed. There will be a line for each month showing the number of records for that month. For example, this could be used in an orders database to find out quickly how many orders were received or dispatched each month. This feature works on editable Date field s and also on Date stamp 8 and Date stamp 10 fields in the Stamp class. Analysing Date fields by month It is, of course, possible for a field containing a date to be index ed. Action (a), above, takes precedence in such a case. You can, however, force action (b) instead by first selecting the field with ADJUST, then choosing from the menu. For cases not described above the menu entry simply says Analyse and is shaded. Printing is always to a window (from which the report may, of course, be saved); the Destination buttons in the Print options window have no effect. Subsidiary indices and printing speed Speeding up printing Try the following experiment using the sample database Elements Create a subsidiary index, if one doesn t already exist, on the Group field (see for the way to do this). make the index case-specific Enter the formula and click Print with ADJUST so that the Match window remains open. Note the time taken for creating the report. Deselect the button and repeat the process. The time will be shorter. You might also notice the brief appearance of a numeral (probably 1) in the small rectangle to the right of the search formula in the Query panel. The speed increase is most noticeable with a slow machine such as an A3000. On a StrongArm RiscPC and with such a small database both times will be so short that the user s reaction might be So what? but when dealing with databases of thousands of records the improvement can be quite dramatic. What happens is that Powerbase detects the fact that there is an index based on the Group (GP) field, goes straight to the first occurrence of in that index then prints records for as long as the key remains . The number briefly displayed in the Query panel is the number of the index being used and its appearance shows the user that a subsidiary index is being used. If you look at the header of the report you will see that it says Ordered by GP index whereas the first time it said Ordered by PrimaryKey . The speed-up only works when the all following conditions are met: The search formula must include a simple comparison for equality, i.e. of the type TAG=target, without alternatives: no OR, no tag list, no target list. OR in search formulae whole of the field represented by TAG must be indexed. switch on the Query panel must be set to agree with the index, i.e. it must be selected if the index is case-specific and deselected if not. +7 ~w7 o2 ~w7 gv ~w7 a ~ 7 * In this section we will often have occasion to refer to text typed by the user. Such text will be distinguished by the use of the Corpus Medium font, e.g. * If this panel is not present type Ctrl M , which toggles the feature on and off. With the default settings of Powerbase the mark panel is displayed but it can be turned off by an option in the Config file (see ). Even so, Ctrl M will always bring it back. Ch 4 Creating a New Database Five steps are involved in setting up a new database: Create the database application shell Design the record layout Specify the number of records the database is to contain. Specify the primary key . Build the empty database Creating the database application shell You need to have Powerbase installed on the iconbar but with no database open, i.e. No data should appear under the icon. Click SELECT over the icon and a Save box will appear containing the default name !Database . Type in the name of your database and drag the icon to a directory window. Remember that, for RISC OS versions prior to 4.00, the name cannot exceed 10 characters, including the initial . If you enter more the name will be truncated and you could end up overwriting an existing database with a similar name. You don t actually need to enter the Powerbase will insert it automatically. Menus directory If you open the newly-created application directory (Shift double-click) you will find that it contains four files ( !Run, !Sprites, !Sprites22 and Data) and five directories ( Indices Menus PrintJobs PrintRes ValTables ). All Powerbase databases expect these objects to be present so don t delete any of them. Designing the record layout When you created the application shell you were left with a window on screen, blank apart from a grid of blue lines, and it is here that you must design the database record. This is the lengthiest part of setting up a database, although efforts have been made to render it as easy as possible. If you have closed the window just click SELECT on the Powerbase icon on the iconbar to re-open it. 4.2.1 Simple field creation Clicking MENU over the window brings up the New database menu on which every item except Create field and Grid is shaded at this stage. Choosing Create field displays the Field Definition window which lets you specify the characteristics of a field. Grid allows you to choose options for the grid used to lay out the fields. This grid appears only at the design stage, not on a working database (except when using the Adjust format and New record format features). It may be turned off but you will probably find it a help in getting the field layout as you want it. There are options to choose the colour of the grid, whether it is represented by solid or dotted lines and what the spacing between the lines is. By default the grid has solid, light blue lines spaced 32 OS units apart and fields will to it at intervals of 4 OS units. The snap interval may be altered or the snapping disabled. First decide what class of field you want to create. There are eight such classes, selected via radio buttons: Editable Computed Scrollable list Stamp Check-box Keypad button External Extra button For the present we will confine ourselves to the first, which is the default selection. As well as the field class you must decide on the type of field within the class. The default offered is Unrestricted , meaning that it will accept all printable characters. We ll look at other types later. Decide on a name for the field and enter it in the Descriptor icon. This is the name which will appear on the record window and may be up to 40 characters long. You must also enter a , which is used to identify the field in search formula e and is limited to 4 characters. Next enter the Data length ; the maximum number of characters the field is to hold. Values up to 246 are allowed. Now click on Create and the field will appear on the record window s probably not where you want it so use SELECT to drag the white rectangle to the position required. When you drop the field in its new position the descriptor falls into place too. If you want the descriptor somewhere other than to the left of the data icon (above it, for example) move it by itself after positioning the data icon. For fine adjustment re-open the Create window by double-clicking on the field and nudge the field into position using the bump icons at the bottom left corner. To edit an existing field you can either click MENU over the field and choose Edit field or simply double-click SELECT over the field itself as above. You can also display the data for any field by clicking MENU anywhere in the window and choosing the field from the Fields Created submenu or by clicking the List fields button on the open dialogue box. After making changes click on Update Create will be shaded). 4.2.2 Deleting, inserting and re-ordering fields Deleting fields Inserting fields It is important to understand the difference between the physical position of fields on the screen and the internal numbering of the fields. The former is purely a matter of appearance and you may drag the fields about the screen to your heart s content, but the latter is fundamental to the way the database will function. Each field has a number which corresponds to the order in which it was created. Whenever you invoke the Field Definition window the title-bar shows either the number of the (new) field you are about to create (e.g. New field 3 ) or the number of the (existing) field you were pointing at when you opened the menu (e.g. Modify field 5 ). The numbering of fields determines the order in which they will be visited by the caret when you are actually using the database and typing Return to get from field to field, i.e. the editing order. A field may be deleted by bringing it up for editing as described above and clicking on Remove field . Fields which come after the deleted field will then be found to have had their field-numbers reduced by 1. Inserting a field into the middle of the existing field-sequence is obviously a little more complicated because we have to specify where in the sequence the new field goes. You need to know the number of the field which you want to follow the new one. This number is entered in the before icon before you click on Create . The layout of this part of the window should make things clear: Create before <n> . If you examine fields which come after the insertion you will see that their numbers have increased by 1. You can change the numbering of a field by removing it and then re-inserting it, but there is a better way. Bring the field up for editing as previously described. We will call this the current field . Enter the number of another existing field in the same place as was used above to specify the insertion position of a new field. We ll call this the entered field . You may then do one of the following: fields, re-ordering Click Swap with . This does exactly what you would expect. It swaps the positions of the current field and the entered field in the field sequence (but not their placement on-screen). Click Renumber as . This is a bit more complicated. The current field acquires the number of the entered field. If this involves giving the current field a lower number than previously the fields beyond the new position are all moved up one place to open a gap for it, at the same time closing up the gap left by moving the current field from its old position. If it is being given a higher number the fields above its old position all move down one place, closing up the gap left by its removal and opening a gap in the required place farther up the sequence. (Once again, the physical position of the fields on the window is unchanged.) 4.2.3 Moving and re-sizing the bounding box Left to itself Powerbase makes all its field icons the same height and of a suitable length to contain the number of characters specified in Data length . This may not be quite what you want. If the data length is large the field could run off the right edge of the window. You might also want to make the field taller for emphasis. (But for displaying multiple lines. Only fields of Text block type can be multi-line. These are discussed later.) bounding box, moving and re-sizing We have already seen how you can alter the position of a field by dragging with SELECT. By dragging at an edge with ADJUST you can change the size of the bounding box. You can also specify both size and position by entering the required width and height of the bounding-box in the and icons and the co-ordinates of the lower-left corner in the and icons of the Field creation window All these values are in OS units (the same units as are used for plotting to the screen) but the origin is the left corner of the record window . This means that the Y values are always negative. Clicking on automatically sets the bounding-box width to fit the data length. 4.2.4 More about and descriptors tags, importance of are very important in Powerbase . They are used when querying the database to produce reports, export CSV files etc. and also by some internal operations. Every printable field (see ) must have a tag and no two tags can be the same. Descriptors are less important. They are there to provide visible labels for fields and in some cases you may not need one at all. e.g. You might want the record to look like this: There are 5 fields here but, having given the second one the descriptor ADDRESS, you don t really want descriptors for the remaining 3 (except perhaps POSTCODE for the last). It is quite in order to have null descriptors like this, but your must give each field a tag. Suitable ones might be NAME, ADD1, ADD2, ADD3, CODE. (Remember each must be unique and not more than 4 characters.) null tags, where permitted omit the tag where the Data length is 0. This allows you to create fields which are simply explanatory labels. Since there is no data in them there would be no reason to include them in a query. 4.2.5 Other types of Editable field So far we have only used fields of Unrestricted type. Clicking on bump icons to the left of the field type, or on the menu button to the right, lets you cycle through the various types available. These are: Unrestricted Accepts any printable character. Alphanumeric Accepts all letters and numerals and common punctuation. Upper case Accepts capital letters and numerals only. Numeric Accepts numerals, +, and . (decimal point). Yes/No/Maybe Accepts Y, N and ? only. Date Accepts dates in the form dd-mm-yy or dd-mm-yyyy, checking that the date is valid and reporting an error otherwise. Accepts times in the form hh:mm:ss up to a value of 23:59:59. The time is checked for validity and errors are reported. Internet Special field for email addresses and web URLs. There is no restriction on character input, but double-clicking will call up your mail program browser (if it has been by the filer) with the address loaded. When Numeric is selected, certain icons in the dialogue box which are normally shaded become available. Thus, you can specify floating-point, fixed-point or integer format by means of a group of radio buttons. You may also specify a maximum and minimum value for numbers which may be entered in these fields. The Numeric min icon is also used to hold the starting value for Sequence number fields (see 4.2.10 Date field should be either 8 or 10 characters long in order to hold the date in one of the two formats specified above. The hyphen separator in these dates may be changed via the Preferences window (see 14.5.1 ). Powerbase is very tolerant of the way you actually enter a date. You may type non-numeric character as a separator: Powerbase will make sense of an entry such as 4/5/87, converting it to 04-05-87 when you type Return. Arithmetic may be performed on dates held in this type of field, e.g. you could have a Calculated field subtract the contents of two Date fields and display the difference in days (see 6.1.5 Time fields also allow flexibility in how you enter the values. If you enter 3.45;9 it will be reformatted as 03:45:09. The colon separator may be changed via the Preferences window (see 14.5.1 ). You may also enter incomplete times which are, by default, interpreted as follows. A number entered on its own is treated as hours. Thus, if you enter 6 and type Return it will be reformatted as 06:00:00. Two number separated by a non-numeric character are treated as hours and minutes, e.g. 6/5 would be reformatted as 06:05:00. You may edit Powerbase s Config file to reverse this behaviour so that 6 is formatted as 00:00:06 and 6/5 as 00:05:06. When using this mode of entry you may also suppress the hours part of the display for values less than an hour, e.g. so that 6/5 is formatted as 05:06. (See for editing Config. time fields, interpretation of input Like Date fields, Time fields may be included in calculations, e.g. to obtain the difference in seconds between two times or to average a number of times (see 6.1.4 scrollable lists, where to use 4.2.6 Scrollable lists These are also user-editable but are treated separately because they are like no other editable field. They are intended for record structures which contain closely-related data whose number might vary widely from one record to another. A music CD, for example, might have only 3 or 4 tracks but it could have 30 or more. If you are cataloguing CDs and including track information you don t really want to define 30 separate fields to allow for just a few extreme records and find even then that the odd disc has more than 30 tracks! A scrollable list lets you make allowance for a modest number of items, adding new ones as required for individual records. Since the list is scrollable it occupies no more space on the record window no matter how many items are in the list. data length in scrollable lists scrollable lists, rows visible You may create this type of field with anywhere from 1-4 columns. The value entered in Data length is the column width, i.e. the number of characters which each cell can hold. All columns are normally of the same width (but see below for how to specify differing widths). When you click Create all that appears is a plain white rectangle: not until you complete the process of creating the database does it appear with the cells and vertical scroll-bar characteristic of this type of field. The height of this rectangle will determine the height, and hence the number of visible rows, in the list. By default it will accommodate 3 rows but you may drag with ADJUST to make it higher. When you release the mouse button the rectangle will snap to a height which exactly fits the nearest whole number of rows. scrollable lists, unequal columns in As noted above, you can make the columns differ in width. The field is created as above and the other steps to produce a working database are carried out. You then need to create a little text file called Format containing the individual column widths, one to a line . There must be as many numbers as columns and the sum of the new column widths must be exactly the same as it was before . Suppose you had created a 3-column scrollable list with a Data length of 10, giving 3 columns, each capable of holding 10 characters. This means you have 30 characters to play with. You can put into Format the numbers 5, 15, 10 or 3, 7, 20 or any other 3 numbers which add up to 30. Where do you put the Format file? Open the database directory (Shift-double-click) and look for a subdirectory called Scroll<n> , where is the field number of the scrollable list. Format should go in this subdirectory. The data contained in scrollable lists is not held in the Database file. Data for each record is in a separate file stored in a special system of subdirectories inside the database directory. In this they resemble External fields (see 4.2.8 4.2.7 Check-box fields These are fields whose status changes when clicked on with SELECT. Five types are defined: Cross/tick Displays a cross initially. A click changes it to a tick. A second click changes it back to a cross. Null/tick Similar to (a), but initial state is an empty box. Null/star Similar to (b), but second state is a star. Null/tick/cross This is a three state check box the first state being an empty box. Repeated clicking cycles through tick, cross and back to empty box. ?/tick/cross Another three-state check-box where the first is a question mark. Check-box es provide the fastest way of entering true/false or yes/no type data and the 3-state types allow for yes/no/undecided situations. If you examine the ValStrings file inside Powerbase.Resources you will find strings associated with each of these five types the latter parts of which read, respectively: QNo,Yes Q-,Yes Q-,* QNo,Yes,- QNo,Yes,? These specify what will actually appear in a print-out when a check-box field is included in a query (see ). You may change them if you wish, but don t omit the initial Q (although this will appear in the print-out) and take care not to alter other parts of the string. 4.2.8 External field External field s allow you to link Powerbase records to pieces of data of a size and type which make them unsuitable for inclusion in an Editable field. Such items are sometimes called BLOBs Binary Large Objects ) in the PC world. The field types in this class and the types of data linked to them are as follows: Plain text files , such as Edit creates. Sprite Sprite files , such as Paint creates. Drawings such as Draw creates. Text block Plain text files, as for (a). Picture Sprite files, as for (b). Remote Anything! When you create a field of type Text, Sprite or Draw it appears on the record window as a button bearing a small version of the icon for Paint Draw respectively. Files of the appropriate type may be dropped on these buttons, whereupon the file is copied into a special system of subdirectories within the database application. Unlike fields of the Editable class the data doesn t become part of the Database within the application (see also 4.2.6 ); the Text, Sprite or Draw file retains its identity and may be exported for editing in the appropriate application. Clicking on the button in the record window will display the file if the filer knows the whereabouts of the relevant editor ( Paint Text block and Picture fields take things a step further by actually displaying a text or sprite file on the record window . The bounding box of the icon needs to be of suitable size to hold the text or sprite. In the case of a Text block too small a box will cause the text to appear truncated. None is actually lost; it just can t all be displayed. Too small a box for a Picture field will cause the sprite to spread beyond its boundaries. (N.B. To display the same sprite on each record, e.g. a company , define the field as of type , not Picture .) the contents of a Text block or Picture field can be loaded into Paint by double-clicking with SELECT. When the edited text or sprite is saved the Powerbase field will be seen to update. Remote fields allow type of filer object (file, directory or application) to be linked to a button on the record window. They differ from Text, Sprite, and Draw buttons in that the linked object is not copied into the database directory; only its pathname is stored, hence the term Remote . This has both advantages and disadvantages. Because no copy is made disc space is saved and the database stays a manageable size; an important factor where large sprites are involved. On the other hand, if you transfer the database to another computer all references to the linked objects become invalid and might even cause retrieval of files which are quite different from the ones you originally attached to the records! Similarly, if you move a linked object to some other part of your hard disc the pathname stored by Powerbase will no longer be correct. A Remote button normally shows a large, down-pointing arrow inviting the user to drop an object onto it. It will accept files of any type, ordinary directories or applications. Once a link is established the button shows the appropriate file icon or a blue folder or the default application icon. A single click has the same effect as double-clicking the object in a filer window: directories are opened, applications are run, files are loaded into their applications, Obey files and Basic programs are executed. If the linked object can t be found Powerbase will be aware that something used to be there and the button will display a question mark. If you wish to break the link between button and file object click MENU over the button, go to the Field submenu and choose Unlink object . The button will revert to its original down-pointing arrow icon. The uses of this field type are limitless. You can use it to catalogue your collection of GIFs, JPEGs, sound samples, Sibelius files, Impression documents or whatever. You could even use it as an application launcher, although I m sure there are more convenient ones available! External fields, unlinking The full pathname of a Remote object is stored in the database. Since RISC OS allows pathnames up to 255 characters long they can occupy rather a lot of space in a large database. Powerbase does allow you to specify a maximum pathlength of less than 255 characters but you must be very careful when using it. The value is set in the !Powerbase.Resources.Config file and is called PathLen . When an empty database is created it will use whatever value of PathLen is currently configured and it may not subsequently be changed without a complete reformat of the database . Be wary, therefore, about reducing the default value of 255 characters. Since, however, it is now possible for databases to have Config files of their own it would be far better to place any lower value of PathLen in such a file and leave Powerbase s default at 255. Any attempt to store an overlong pathname in a Remote field will be rejected with an error message. 4.2.9 Computed fields Discussion of Computed fields is postponed to Ch 6 Performing Calculations. 4.2.10 Stamp field Stamp field s resemble Computed fields in that you cannot edit them; Powerbase stamps the fields with the appropriate contents automatically. The following types are available: Record number Database record number. Sequence number Unique value numbered upwards from base value set by user. Time Time at which record was created. Date Date on which record was created. Date and time Date and time of record creation. Day on which record was created (in the form Sun, Mon, Tue etc or 1, 2, 3 etc, or day of month as 10, 24, 31 etc). Month Month in which record was created (as either a string; Jan, Feb, Mar etc or a number; 1, 2, 3 etc). Year Year when record was created as four-digit number, e.g. 1993, 2000 Last altered Records the date and time record is first created and updates it only if the record is subsequently altered. Merely displaying the record does cause updating. Allows a sprite to be included as a on every record. These fields are stamped by Powerbase when a record is first entered and thereafter, with the exception of (i), stay fixed. (But see for similar types of field which auto-update.) Note the following: When defining Record or Sequence number fields make sure the Data length icon contains a large enough value to accommodate the longest number which will be encountered. For the other types the required field length is already known by Powerbase and the Data length box is therefore shaded. The base value from which sequence numbers begin is entered in the Numeric min box. When a record containing such a field is deleted the sequence number is normally re-used; a new record is given a new sequence number. This leads to gaps in the numeric sequence and you might want to reassign the numbers so that the sequence is continuous. You can do so by means of Compact sequence from the Field submenu. Before this can be used the sequence number field must be indexed and selected as the current index. Date fields may display the date in any of three formats: Sun,01 Aug 1993 (called Long ) 01-08-93 (called Date 8 ) 01-08-1993 (called Date 10 (2) and (3) are identical to the formats in which Powerbase displays dates of the ordinary Editable type (see 4.2.5 ). The numbers refer to the field length occupied by the date stamp Logo fields require the name of the sprite to be entered as the tag of the field to be used as a . This means that the sprite name is limited to four characters. The icon for a Logo field (like that of a Picture field) must be large enough to hold the intended sprite. You may have several fields on your record, all the required sprites being included in a sprite file called UsrSprites which is placed inside the database directory. 4.2.11 Button field Any or all of the control buttons on the Powerbase keypad may be made to appear on the record window itself. They have exactly the same functions as the keypad equivalents. These button field s let you build a customised database which allows the user to use only the features you want him/her to have access to since the keypad and menus can then be suppressed (see ). There is also a group of Extra buttons which have no keypad equivalent: Print brings up the Match window for report printing. Print button (on screen) duplicates the action of Close database on the iconbar menu. Exit button Quit duplicates the action of on the iconbar menu. Quit button Run file button, unlinking Run file This type of button displays an icon made up of four different filetype icons. When you drop a file onto the button the icon changes to that of the relevant file and subsequent clicks on the button will run the file. Text files are treated as Powerbase scripts (see Ch 12 ), other types of file (e.g. Obey files) have their normal Run action. It is important to appreciate the difference between this type of button and a Remote field button (see 4.2.8 ). The file associated with a Remote field belongs to a specific record and every record can have a different file. The file attached to a Run file button is the same whatever record is being displayed and merely provides a convenient means of executing the file. To break the link with the file click MENU over the button, go to the Field submenu and choose Unlink file. The name of the file, if required, may be given in the descriptor or the leafname may be made to appear on the button by editing the Config file. Directory This resembles the previous type. In its initial state the button displays the small directory icon. Dropping a directory onto it changes this to a large directory icon and clicking on the button opens a filer window on the directory. To break the link with the directory click MENU over the button, go to the Field submenu and choose Unlink directory Options in the Config file allow you to determine what the filer window looks like; the default being small icons and alphabetic sorting. The leafname may be displayed on the button. Without it you could confuse Directory Run file buttons with Remote fields. Menu button on screen This button lets you associate a data field with a pop-up menu . Clicking the button and choosing a character string from the menu enters it into the data field. The field number of the data field is entered as the of the menu button. This happens automatically if you define the button immediately after the data field. The menu data is in a text file whose name is the tag of the associated data field plus the word . Put the menu title on the first line of the file and each menu choice on a separate line. All user-menu files are stored in the Menus subdirectory inside the database directory. If you click on a Menu button without having constructed the relevant menu file you will be given appropriate instructions. User menus, defining 4.2.12 Mandatory fields mandatory fields, colouring of This isn t yet another class of fields. A mandatory field is one which be filled in before you can access another record or close the database. It has already been noted that at least one of the primary key fields must not be blank (see 2.4.1 ), but any Editable field (but not Scrollable list) can be made mandatory by selecting the Must not be blank option button when designing the field. Such fields appear on a working database with red as the foreground colour. (This may be altered via the Colours window ; see A short-cut to a working database The third item on the New database menu is called Default database . Choosing this is by far the quickest way of getting a database up and running. Its action is to create three files inside the application directory. These are called Form (which holds the record design), PrimaryKey Database (which will ultimately contain the entered records). The number of records in the database is set to 100, with 25 as the amount by which this should increase when the database becomes full. The primary key is defined as the first four characters of the first Editable field . The database is opened and a blank record displayed ready for data entry. Since you can always alter such things as the database size and primary key structure later, you might wish to use these defaults while you experiment with the database. If you want to choose the database size and primary key structure yourself follow the procedure in Sections and Specifying the database size This involves two steps: Save the Form (which contains all the field data). The Save form file menu entry leads to a standard Save box but, since the pathname is correctly set for saving the file inside your database application, all you actually need do is click on the menu item itself. You will now see that the Database size choice is no longer shaded and may be used to reach the Size window in which you specify the number of records in the database and the increment for expanding the database when it becomes full. Specifying the primary key Primary key, defining structure 4.5.1 General procedure Click MENU and choose Primary key This opens the Key Structure window. The primary key (or any other key) is derived from one or more record fields called key field s. Up to four key field s may be used to define a key but we will begin by using just one. Four pieces of information need to be specified: Choose the field , either by clicking on the bump icons or choosing from the associated pop-up menu. The default is the first Editable field in the record. Enter the within the field from which characters are to be taken to make up the key. The default is word 1. If 0 is entered word boundaries are ignored (see 4.5.2 Enter the position within the word from which characters are to be taken. L (default) means from start of word, R means take from end. A number ( ) means start at the th character. Enter the number of characters to be taken from the word. The initially-set value is 4 but thereafter the default is the defined field length. After entering this information click on Create and the empty Database PrimaryKey files will be created, after which the database will open ready for you to start entering records. 4.5.2 Some illustrative examples Key field s should be chosen with care. An ideal key field is one whose contents would never be repeated in another record. Powerbase lets you enforce this condition if you wish (see 11.2.1 ) but the default setting allows key duplication. Occasional repetitions may not be serious, but a field which can have only a few values is usually a poor choice. A customer number or membership number is the sort of thing we are looking for but your database may not contain anything like that. In a database of school pupils the pupil s name would be a good choice of key, but the form teacher s name would not, since only a small number of names would be involved, each appearing on the record of many pupils. Suppose you decide to use a person s name, stored surname first, in a single field whose tag is NAME, as a key. You could use the whole name but it would probably be too long. The first four letters would be more appropriate but, as this is the start of the surname, you might get a lot of duplication. e.g. PRESTON and PRESCOTT would both have the key PRES; and this is before we even consider people whose surnames are identical. To get round this problem, Powerbase lets you construct an alphanumeric key from characters taken from up to four successive words. You will seldom need to go so far. In the present case a five-letter key made up from the first four letters of the surname and one letter of the forename would be good enough for most purposes. Set up the Key Structure window as follows: Key structure, examples Field Word Position Chars 1st row: 2nd row: This works quite well. Smith Peter and Smith Janet would have the SMITP and SMITJ. Duplication can still occur, but not often enough to be a serious problem. Further examples illustrate the use of letters from different parts of words in the key field. The following settings of position characters would produce the shown from the name Herring Albert. (a) (d) use the Surname only, (e) and (f) use the Forename only, the rest use both names: From Surname From Forename Word Pos Chars Word Pos Chars HERRI (stops at word end) HERRALBE ALBING RINER GALBERT (ignores breaks) RINGAL (ignores breaks) Take particular note of what happens if the word number is entered as 0 (or left blank). All breaks between words are then ignored. The field is treated as if the blanks between words had been removed leaving a single long word which is then subjected to the process specified by the position and characters icons. When, on the other hand, the word number is 1 or greater the scanning for characters stops when the end of the word is reached so that the key might be shorter than the character length specified. Example (d) illustrates this. 4.5.3 Using more than one field in a key You might, of course, want to store surname and forename in different fields. Let s call their SNAM and FNAM. To produce the five-letter keys given in our first example the key definition would be: Field Word Position Chars 1st row: 2nd row: and the generated will be exactly the same as they would be for the single field NAME. There are databases where no single field is suitable for constructing the primary key . In a database of classical music, for example, there would probably be one field for the composer s name and one for the name of the work. Neither is much use individually; the former could contain many occurrences of Mozart or Beethoven and the latter many repetitions of Symphony No. 5 or String Quartet in D minor. This is the kind of situation where you might want to use two fields and as many as four words, taking one word from the Composer field and three from the Work field using, say, 4, 3, 3 and 2 characters respectively from them. The following two works then give the shown (characters taken from the left in each case): key structure, multiple fields in BEETHOVEN Symphony No 5: BEETSYMNO5 MOZART Piano concerto 23: MOZAPIACON23 Note the following points in these examples: If a word is shorter than the number of letters assigned to it ( in the first example) then the whole word is used but no padding is inserted. It may be necessary to omit an insignificant word ( in the second example), in order to make a significant one ( ) come in the first four words of the combined key fields. It is not usually sensible to build up derived from Numeric field s out of separate bits in this way and, indeed, Powerbase t allow you to do so. Numeric should use one key-field only, the key being the actual numeric value of the field contents. Case and indexing Index, case-sensitive 4.5.4 Other matters concerning keys We have already noted that a word shorter than the number of characters specified is used just as it is, resulting in a short key. There might be times when you want short words padding out with spaces to give a key of full length and there is a switch to select this action in the Key Structure window. A final factor to be decided is whether the index ing is to pay attention to the case of letters, i.e. if they are capitals or small letters By default index ing is case-sensitive. Thus if a record has the word Horse as the contents of a key field and the first four letters are used as the key then the entry in the index will be and you may search for it by entering etc. This may not be what you want. If you require index ing to be case sensitive select the switch labelled . Forcing to upper case as described above does not then take place: are inserted and strings are searched for as is . The field containing Horse will be index ed as and only that precise combination of upper and lower case letters will successfully find it. Alphanumeric will be ordered according to the ASCII values of the letters. Since lower-case letters come later in the ASCII table than upper-case ones a record containing would appear after one containing Horse , whereas one containing would come before Horse Building the empty database Once you are satisfied click Create and blank Database and PrimaryKey files will be created. The record window is redrawn and you may start entering data at once. empty database, building Renaming a database Use the Rename database option on the Miscellaneous submenu to do this. If you rename via the filer your database will lose its icon and revert to the default application icon ( with Archimedes , or 2 large exclamation marks if you have RISC OS 4). Rename database is duplicated on the iconbar menu so that you can rename at the record-design stage. Database, renaming will These three may be reduced to a single mouse-click if you use the Default database option NAME Fred Bloggs ADDRESS 27, Every Street Anytown Woolshire WL4 7XZ * ASCII stands for American Standard Code for Information Interchange. Each uppercase letter, lowercase letter, numeral, punctuation mark etc. corresponds to a number in the range 32 255. (Numbers below 32 are non-printing control characters.) e.g. the numerals 0 9 have consecutive ASCII values from 48 57, the uppercase letters have consecutive values 65 90 and the lowercase letters from 97 122. The numbers are used to store the characters within the computer and on disc. Ordering strings according to their ASCII codes means that those beginning with a numeral will come before those beginning with an uppercase letter and the latter will come before those beginning with a lowercase letter. Ch 5 Input Validation and Validation Tables Powerbase can apply two kinds of data input validation Character validation Table validation Character validation This means exercising control over what characters a field will accept. RISC OS provides the means to allow writable icons to accept only certain characters, or ranges of characters, and reject others. The disallowed characters are simply ignored so that attempts to type them have no effect. If you Shift/double-click on the Powerbase icon the contents of the application directory will be displayed. In it you will see an ordinary directory called Resources and inside Resources you will find a file called ValStrings . You are recommended not to alter this file unless you understand exactly what you are doing and to keep a copy of the original. Examining it can, however, give you an insight into the validation process. Look, for example, at the entry 03 Number . This refers to field type 3 (Numeric). The line below is the validation string of the writable icon which makes up a numeric field . It looks like this: A0-9.+\- ;Pptr_write,4,4;KD The initial is a command which signifies Allow and is the part of the validation string which especially concerns us. It determines what characters the field will allow you to type. In this case they are the numerals 0-9, the decimal point, plus and minus signs and <space>. Why the back-slash before the minus sign? Because is a special character in an icon validation string (used, as in this example, to specify a range of characters) and so are the semicolon, tilde (~) and the backslash itself. To include any of these four characters in the validation string you must precede it with a backslash. The next character is a semicolon which indicates that a new command follows. which comes next is the command pointer ptr_write is the name of the sprite used to represent the mouse pointer when it is over this type (and many other types) of field. You will have noticed that the usual RISC OS arrow changes to a blue vertical bar, somewhat resembling the caret, when over a writable field. specifies the active point of the pointer. A couple of further examples should be sufficient. 01 Alphanumeric uses the same pointer type but the characters accepted are <space> (immediately after the for allow ), the upper-case letters A-Z, the lower-case letters a-z, the numerals 0-9, some common punctuation and the character. You might want to add to this list. 06 Calculated uses a different pointer sprite (ptr_calc, which resembles a pocket calculator) and has no Allow command at all. (You can t type into this type of field; its value is calculated from other fields.) Validation tables Table validation is used where a record field is only allowed to have certain values . A manufacturing company, for instance, will have a product code for every item it makes and a database of customers which the firm supplies will make use of such codes to identify the items. It is essential that whoever enters data is prevented from typing a spurious code. This can be achieved by linking the appropriate field to a validation table . Fields linked in this way are recognizable by having their text dark green instead of black. Powerbase will allow you to type invalid data into the field initially but, when you click the mouse over a new field or type Return, the linked validation table is scanned to see whether what you entered in the field is on the allowed list . If it isn Powerbase will restore the previous contents of the field and print an error message. Validation table s have other uses too. They can have extra columns containing other data about the products, such as a name and a brief description, and lists can be printed in which this more informative data appears instead of the codes. You can also have Powerbase replace the typed-in code with a longer, more readable, form immediately on entry. Provided the substitute data will fit in the field, replacement occurs when you type Return or click the mouse in another field. If the replacement is too long then the typed-in data will be unchanged. This can be a great timesaver when a lot of data has to be entered. The F.E. college database referred to in 2.2.5 makes extensive use of validation table s for subjects, courses, tutors and schools of origin. Not only does this trap typing errors but the data entry is greatly speeded up when one can type instead of English Language and Literature , yet have the latter printed out on a list by selecting the Expand switch in the Print options window. Creating a validation table Choose Create table from the Validation submenu. Give the table a suitable name and enter the number of rows required. This will be the maximum number of items on the allowed list . (It is possible to increase the number later if necessary.) Next place the caret in the first row (row 0) of the scrolling list and enter the width (in characters) and the heading (optional) for the first column of the validation table If the table is to be used to constrain data entry to an allowed list one column may be all you need. The most common situation, however, is to have two columns, the first holding the allowed list and the second being an expansion or explanation of the former. Use the next row of the scrolling list to enter the column width and heading for the second table column. It is possible to use very large and complex validation table s with all kinds of data associated with each item on the allowed list . There is room to specify 20 columns, but even this can be increased if necessary (see You can if you wish choose the foreground and background colours of both the heading and the body of your table by clicking repeatedly with SELECT or ADJUST on the Heading and icons. Whether this changes the foreground or background colour depends on which of the two radio buttons is selected. When you are satisfied with the data click Create and the table will be created and displayed. It is not saved on disc at this stage. You may enter data into it now or later. Entries may be freely altered and overwritten. The table will be saved when you close the database or quit Powerbase but, if you want to play safe, click Force update on the keypad Linking tables to fields To link your newly-created validation table to a field click MENU over the field and choose Link to table from the Field submenu. A pair of bump icons, with the usual pop-up menu alternative, lets you cycle through the tables in memory. When you have the name of the required table displayed choose the column of the table to which the field is to be linked. It is recommended that column 0 normally provide the link (and therefore contains the data items for the allowed list ) and this is set by default. You may, however, link to any column in the table and another pair of bump icons lets you cycle through the column numbers. Select Linked to table and click on . You will see that the foreground colour of the linked field has now changed from black to green. Place the caret in the field and click on List values on the keypad . The validation table should be displayed. Scrollable lists and validation tables You may link a validation table to a scrollable list. The link is to a specified column of the list (make sure the pointer is over the required column when you click MENU) and, at present, only column may be so linked: you can t link two different validation tables to two columns of the same scrollable list. 5.4.1 Replace on entry feature Validation tables, replace on entry feature If you select this switch before linking the table a third pair of bump icons becomes active, allowing you to choose which column of the table will replace the data which you type in. In the F.E. college database referred to in the short subject codes (e.g. CHE) would be in the first column and the longer names (e.g. Chemistry) in the second. On typing <Return> Powerbase would replace it with Chemistry. In such cases both the entries in the allowed list proper and those in the replacement list are considered equally valid and either may be entered. You could actually type Chemistry if you wished, instead of just , but the former would obviously take longer to enter and you are far more likely to make a mistake, in which case Powerbase would object. 5.4.2 When to turn off the Exact match switch Validation tables, exact match Exact match switch is normally selected to indicate that the only user inputs which will be accepted are those which exactly match an entry on the allowed list . Deselecting the switch allows you to type inputs which are longer but not shorter than the entries on the allowed list so long as the leftmost part of the input matches such an entry. The unmatched of your typed entry will be attached to the end of the replacement string. This feature is intended for use with the Replace on entry facility. Suppose your database records numbers of items called Widgets, Doodahs and Thingummies. You decide to identify these names with the codes W, D and T, put the codes in the first column of the table and the names in the second and select Replace on entry . You can then type <Return> and it will be replaced with Widgets. What you might want, however, is to type and have it replaced with Widgets, 7. Deselecting Exact match allows you to do just that since the W in W7 matches an entry in the validation list. The expanded entry in the second column is made to read Widgets, (note the comma and space) and Powerbase tacks the unmatched part of your entry onto the end of this so that is replaced with Widgets, 7. validation table, displaying Displaying validation table Placing the mouse pointer over a linked record field and double-clicking with SELECT makes a small window pop up to the right of the field. This shows all the data which is on the same row of the validation table as the linked item. The item from the allowed list is highlighted in green and the item (if any) to be substituted on entry is shown in red. This feature is very useful if you are examining a database which uses coded data and you encounter a code which you haven t seen before. You can also make this window appear automatically whenever the caret enters a linked field. To turn the feature on choose Preferences from the iconbar menu and select the Display linked table data switch. When linked to a field in the database record the complete table may be displayed by placing the caret in the linked field and clicking on the keypad button List values ). Alternatively, any table present in memory, whether linked to a data field or not, may be displayed by selecting it from the Display table submenu (reached from the Validation submenu). validation table menu Clicking MENU over a displayed table offers a menu with entries as follows: Clear removes all data from the table, leaving it blank. Since wiping out a table in this way is pretty drastic you will be asked to confirm the operation before it actually takes place. It is possible to recover the table using all provided the table has not been closed. When you close a database all the validation table s in memory are written to the disc so you will over-write your disc copy with the blank table. Modify brings up the same window as you used to create the table. You may then increase the number of rows, add extra columns, alter the order of existing columns or, indeed, do anything sensible. Be careful about renaming columns; combining this with shuffling the columns about is apt to cause confusion: Powerbase t a mind-reader! When you have made the required changes click on Modify Print outputs the contents of a validation table in the same format as a report. The output appears in a window from which it may be saved as a text file (see 3.1.1 <n> will sort the table on the th column, that being the one the mouse pointer was over when you clicked MENU on the table. change will restore the specific item which you were pointing at when you clicked MENU to the state it was in when the table was displayed. You cannot changes if you have closed the table then re-displayed it. all restores the entire table to the state it was in when first displayed, even if Clear has been used. You cannot changes if you have closed the table then re-displayed it. leads to a Save box in which the default pathname points to a directory called ValTables inside the database application directory. Since that s where Powerbase expects to find the tables for a database you should normally accept this pathname by clicking on or typing Return. Only if you are transferring tables from one database to another should you need to drag the icon to a filer window. validation table s in memory are, in any case, saved to the ValTables directory when you close the database or quit Powerbase and you can also make this happen at any time by clicking on Force update on the keypad Save as CSV leads to a Save box from which the table contents can be saved as a CSV file . The default pathname points to the database PrintJobs directory. The file can be loaded into a blank validation table of appropriate format by dropping the file onto the open table (see for further details). validation table, save as CSV Loading validation tables A table linked to a field is automatically loaded when the database is opened. If not yet linked, however, the table won t be in memory unless you have just created it. To load the table choose Show files from the Validation submenu ( Ctrl Q ). The contents of the ValTables directory will be displayed and the required tables can dragged onto the record window (or simply double-clicked). As each table is loaded it will be displayed. validation tables, loading unlinked unlinked tables, how to load There may be times when you wishes to use a validation table to hold some data, but don t want to use it to validate input into a field or to link it to a field at all. It is possible to load a validation table once the database is open by using the procedure described in the previous paragraph, but a more convenient solution is to add a character to the end of the table s name (the usual limit of 10 characters applies to the name length). Powerbase will then load it whether any field is linked to it or not. Including validation table data in printouts Validation table s are often used to allow short codes to be entered in records but with a link to a more descriptive entry in another column of the table. If this is all that is required then always put the data which makes up the actual allowed list , i.e. the items which are allowed in the fields of the main record, into the first column of the table (column 0) and set the link from the record field to this column. The more detailed expanded entry should go in column 1. Printing with the Expand switch in the Print options window selected will then print the column 1 instead of the column 0 entry. You can also include data from other columns of a table by displaying the table and clicking in the required columns with ADJUST, exactly like selecting main record fields for printing (see ). It is immaterial which row you click on; only the column matters and the highlighting to show which columns are selected always appears in the first row. The columns selected in all tables are saved as part of a print Selection file and may therefore be retrieved for future use. As well as highlighting the required columns you will also need to select the Expand switch as described above. Printed reports will then include all the data from the highlighted columns. You will need to use this method if you want to print columns 0 1 of a table instead of printing column 1 instead of column 0 as described in the previous paragraph. Entering validation table data into a record There is one more feature of validation tables which might prove useful. If you place the caret in a database field and then double-click over an entry in an open validation table, the table entry will be copied into the data field, if it will fit. Nothing is copied if the item is too long to fit. mmate * If you have closed the database the table won t have been reloaded on opening again. See for how to load your table for linking. * This feature cannot be used on tables created with early versions of Powerbase and the option will be shaded in such cases. The commonest reason for needing to modify such a table is to increase the number of rows. First export the data as a CSV file then create a new table with the required number of rows and drop the CSV file onto it. Ch 6 Performing Calculations There are two mechanisms for performing calculations Powerbase Self-calculating fields which automatically calculate and display a value derived from other fields. These belong to the field class called Computed (see 4.2.9 Arithmetic performed on a column in a report. Computed fields may be of Calculated or Composite types. Calculated field s display a numeric result derived from other fields in the record. When the mouse pointer is moved over such a field it changes into a shape resembling a pocket calculator. Composite field s perform a similar function but display the result as a character string instead of a number. In this case the mouse pointer changes into a large sign. The latter do not, strictly, involve calculations at all but because Composite and Calculated field s work in the same way it is convenient to deal with them together. It isn t possible to type data directly into either of these special types of field. There are also Computed fields for the same types of data as Stamp fields of types (c), (d), (e), (f), (g) and (h) (see 4.2.10 ). They have identical names but, whereas Stamp fields are entered when the record is created and don t change thereafter, the Computed types update whenever a record is displayed It is also possible to write your own functions in Basic and use them in Calculated and Composite field s. The system variable TIME$ is useful in this connection. Calculated field 6.1.1 Simple calculations using Numeric field Our first example will be to make a field containing a v.a.t. exclusive price determine the contents of another field which includes v.a.t. at 17%. Let the of these fields be VEX and VINC respectively. Click MENU over VINC to bring up the Field submenu and choose Calculations . A window appears with a writable icon which contains: VINC= Complete the formula so that it reads: VINC=VEX*1.17 and click on or type Return. From now on the value in VINC will change whenever you type something in VEX. Since the v.a.t. rate is liable to change it would be better to place the current percentage rate in another Numeric field called RATE and make the formula attached to VINC read: VINC=VEX+(VEX*RATE/100) The second example calculates the average of four fields No1, No2, No3 and No4, placing the result in a Calculated field AVGE. The required formula is: AVGE= (No1+No2+No3+No4)/4 You may also enter a tag into a calculation formula by clicking on the required field with Ctrl held down or by choosing it from the pop-up menu of fields available from the calculation window. The tag will be entered in the formula at the caret position. You are strongly recommended to use one or other of these methods since Powerbase finds it very difficult to decide whether or not a formula is valid and errors are usually detected only when the calculation is actually attempted. 6.1.2 Making calculations retrospective The default setting is such that entering or altering a formula affects only those records added or altered after the formula entry/change. The changes can be made retrospective by selecting the Recalculate existing records switch on the formula entry window. On clicking you will be asked to confirm that you want previous records to be made consistent with the formula you have just entered. Changes affect the current subfile only, but can be easily implemented in other subfiles by changing to the required subfile, calling up the formula entry window, selecting the option button and clicking The Preferences window, accessible from the iconbar menu, contains a switch labelled Recalculate on opening . If this is selected then calculations involving the system variable TIME$ will be updated automatically for all records when the database is opened (see also ). This could be needed where calculations involve dates, ages, times etc. which might change from one work session to the next even though no editing of the data has been done. 6.1.3 Calculations using numeric field s (!) This isn t as daft as it looks! You might think that the process described in 6.1.1 is only applicable to numeric field s, but a non- numeric field may be specified in the formula it is linked to a validation table . To be of use there must be numeric data in the column of the validation table immediately following the one to which the field is linked (see ). Suppose, for example, we have fields in a student record for A-level exam grades, the field being GR1, GR2, GR3, GR4. The grades are non-numeric but they map onto the numeric points system which universities use to control entry. The relationship of grades to points is as shown at left. A validation table could be set up with the grades in column 0 and the equivalent points in column 1. Each of fields GR1 GR4 would be linked to column 0 thus restricting input to the capital letters A-E. A further field of Calculated type would be created to hold the points score. If we associate this field (PTS) with the formula: PTS=GR1+GR2+GR3+GR4 entering or changing the letter grades in GR1-GR4 will cause the correct points score to appear in PTS. 6.1.4 Calculations involving times There is a field type Time which will only accept valid 24-hour times in hh:mm:ss format. If the tag of such a field is included in a calculation formula it will be converted into seconds and the result used in the calculation. Thus a Calculated field DIFF could use two Time field s, TIM1 and TIM2, in the formula: DIFF=TIM1-TIM2 Powerbase would keep DIFF updated to show the difference in seconds between the two times. Three times could be averaged and the result (in seconds) placed in a field AVGE using the formula: AVGE=(TIM1+TIM2+TIM3)/3 6.1.5 Calculations involving dates You may also include date fields in a calculation in a similar manner to the use of Time fields (see 6.1.4 ). A Calculated field DIFF could be used to show the number of days between two dates using the formula: DIFF=DAT1-DAT2 Composite field To enter the formula for a Composite field follow the same procedure as for a Calculated field . Note that the relevant entry on the Field submenu now says Combine fields . Formula entry is similar to that for Calculated field s. The result of the calculation is a character string and is usually result of string operations. One of the most frequently used will be which allows fields to be joined together. Suppose your database contains fields for surname (SNAM) and forename (FNAM) and you want to be able to print names in the format forename-surname. Define a Composite field called NAME and attach to it the formula: NAME=FNAM+ +SNAM Note the quoted space separating the names. You might want the NAME field to show only an initial plus the surname. This could be extracted using the Basic function LEFT$ * to produce the formula: NAME=LEFT$(FNAM,1)+ +SNAM Composite field s may be used in conjunction with Time field s to perform genuine clock arithmetic , e.g. referring to the examples in 6.1.4 , if we attached the formulae to Composite fields instead of Calculated fields they would display the difference and average respectively in hh:mm:ss format instead of in seconds. As with Calculated field s updating occurs after editing a field whose tag appears in the attached formula. Thus NAME would be updated after changes to FNAM or SNAM and DIFF after changes to TIM1 or TIM2. You can, however, have Composite field s which make use of the Basic system variable TIME$. Thus a field DAY could be linked to the formula: DAY=LEFT$(TIME$,3) to make it show today as Mon, Tue etc. No field are referred to in the formula so DAY gets updated immediately before displaying the record so that the information is correct at that time. User functions These are functions, written in Basic, which accept field as parameters and can be included in the calculation formulae self-calculating fields. All such functions must return only their principal value: RETURN variables in the parameter list aren t allowed. The name of each function must begin with an upper-case , e.g. FNUaverage. This avoids duplication of function-names which occur in Powerbase All the user functions to be used by a database must be included in a Basic program called UserFuncs which is stored in the database directory (not in the Powerbase directory). When the database is opened UserFuncs will be loaded as a library and Powerbase can call the user functions just as readily as its own functions. When a user function appears in a calculation formula the tags of any fields on which the function operates are included as parameters to the function. Editing any such field makes the Computed field update. The distribution disc includes a UserFuncs file containing two examples of user functions kindly submitted by David Lenthall. Users are invited to submit other user functions for possible inclusion in the function library. FNUnow operates on the value of TIME$ from the real-time clock and returns a string containing the current date in form DD-MM-YY. A record design could include a field of Composite type, at least eight characters long and tagged as, for example, DATE. Associating the field with the following formula would cause the field to display the current date at all times: DATE=FNUnow(TIME$) If the function always operates on TIME$ and never on any other string, why do we bother including TIME$ as a parameter to the function? A Calculated or Composite field is normally only updated when you edit a field on which its value depends. The field DATE in the above example doesn t depend on any other fields and would therefore never be updated! The inclusion of TIME$ causes the Composite field to be updated immediately before the record is displayed. The same trick of using TIME$ as a parameter, even if the function makes no use of it at all, can be used for any user function associated with a field which you want updating without having to edit the record, but note that updating will only occur if you call up the record for display. See 14.4.3 for how to make records in the current subfile update on opening the database. The second function, FNUageinyrs accepts two parameters, each of which should a date in DD-MM-YY format, and returns the difference between the dates to the nearest year. The first date should be the earlier of the two. If your record design has a field for Date of Birth (let s call its tag DOB) you can use this function together with FNUnow (which returns today s date in the required format) to make a Composite field (AGE) display a person s age in years by using the formula: AGE=FNUageinyrs(DOB,FNUnow(TIME$)) Considerable care is needed in constructing user functions as it is very easy to make Powerbase generate errors. In particular you must avoid giving a function a name which is the same as a field tag or even one which contains a field tag as a substring. The two functions described could not, for instance, be used in a database which had a field tagged as , although and could be used. To avoid this problem users are recommended to form the habit of giving tags names in upper case and user functions names in lower case (apart from the , of course). Calculations on a column of a report There is an option on the Print submenu called Numeric field which is normally shaded. It only becomes available when you highlight one or more Numeric or Calculated field s for printing. You can then access a window listing all the fields (if any) in the record which are of these two types. Associated with each field are six check-box es which can be selected to include the following in the report: column calculations Count (number of values in column) arithmetic on report columns Average Standard deviation Maximum value in column Minimum value in column check-box es are shaded until a field is included in a print selection. They then become and any or all of them may be chosen by clicking with SELECT. The information requested is then added to the report footer when printing takes place. ns on a * LEFT$(string$,n) extracts the leftmost n characters from string$. RIGHT$(string$,n) extracts the rightmost n characters. MID$(string$,n,m) extracts m characters starting at the n th character from the left. Grade Points 10 8 6 4 Ch 7 Using extra indices The record data is stored in a file called Database inside the database s application directory. The order of records within Database is determined by the order in which the records are entered. To access the records in a logical order we need one or more indices Powerbase does have sorting facilities for sorting reports and validation table s, but data ordering is achieved mainly by the use of indices The most important index index 0. This is always called PrimaryKey and is created as part of the process of setting up a database (see ). You will find the file PrimaryKey inside the database directory. Other, subsidiary indices are stored in the database Indices directory and all have a large on their icon. When a database is opened any indices in this directory will be loaded automatically. ( Indices operate in memory to achieve maximum speed not from disc.) Changes to the database update the indices and closing the database (or quitting Powerbase itself) causes the updated indices to be written back to the disc. Note that the more indices you use the longer it takes to add and delete records or to make alterations to records which involve changing indices . The ideal situation might seem to be to have fields index ed and, given a fast enough machine, a hard disc and a record structure with not very many fields, this might be feasible. Remember that fields which are repetitive (those which have only a few distinct values repeated throughout many records) do not index efficiently. subsidiary indices Subsidiary indices can speed up report printing in some cases (see ). When you enter a query Powerbase will see if a subsidiary index can be used with advantage and, if so, will go ahead and use it. To indicate that this is happening the number of the subsidiary index will appear in the small rectangle at the the top right of the query panel. For the speed-up to work the case button on the query panel must match the case-dependence of the index. This is a point easily overlooked! Indexing a field Click MENU over the required field and choose Create Index (Ctrl J ) from the Field submenu. The Key structure window will appear with the tag of the field you clicked on in the first of the four Field icons. You may use the bump icons or pop-up menus to bring the of the other fields into view if you wish to base the index on more than one field. Enter the data in exactly the same way as when creating the primary key index (see ) and click on Create . If the field is already index ed you will be warned of this and asked if you wish to overwrite the existing index . When index ing is complete you will see that the descriptor of the index ed field has changed from black to dark blue. If you click the buttons on the keypad Shift F4 and Shift F5 ) the red highlighting of the descriptor which indicates the current key moves from one index ed field to another. When you have a subsidiary key selected as the current key it works just like the primary key as regards browsing, searching and printing. There is nothing to stop you index ing one field whilst a subsidiary index based on another field is the currently-active one, thus producing what amounts to a sort within a sort. null keys in subsidiary indices One button which was shaded when creating the primary key is the option switch Omit null keys . Although null primary keys aren t allowed null subsidiary keys are and the default setting for this switch is therefore OFF. The field being indexed might in some cases be null most of the time and you want to index only the records in which it isn t. If so, select the switch before clicking Create. Index entry on the main menu leads to a submenu offering three choices. Show details Ctrl K ) displays the structure of the currently active key. Show files Ctrl I ) opens the Indices directory. Delete allows you to remove an index (after confirmation). The index file isn't actually deleted from Indices but simply renamed so that it has the prefix . Such an index is no longer active and will not be loaded when the database is opened. You can, however, restore it by removing the prefix but it is only useful to do so if no records have been added to or removed from the database in the meantime. Automatic saving of indices Indices are written to disc when you close a database or quit Powerbase but, as long as the database remains open, they are in memory and therefore could be lost if the power fails or the computer is switched off. Occasional use of the keypad Force update button ensures that the disc copy of all indices is more or less up-to-date but you can automate the process if you wish. Choose Preferences from the iconbar menu, make the appropriate selection from the Save indices section of the Preferences window and click on Accept . You may make Powerbase save the indices at regular intervals or simply warn you to do so. Ch 8 Using CSV files What are CSV files CSV stands for comma-separated values and is the name given to files consisting of lines of data, each of which contains individual data items separated from each other by commas. Such files are widely used to mail-merge using a wordprocessor (see ) and also to transfer data from one application to another, e.g. from a database to a spreadsheet or from a RISC OS database to a PC database. We will refer to each line in a CSV file as a record and each item of data in such a line as a field since records and fields are the source and destination of such data when it is exported from or imported into a Powerbase database. The following points should be noted: CSV files, description of Fields which are non-numeric (e.g. plain text items such as names and addresses) are often enclosed in double quotes ( ) whereas numeric data is not. This makes it possible for an application reading a CSV file to distinguish between numbers and strings (which might of course contain numerals) and also allows a comma to be used as a character within a string without being mistaken for a data separator. (Addresses often contain commas e.g. 112, Keighley Road). For many purposes the quotes aren t necessary and may be omitted. Null fields are usually included and can be located by looking for two commas with either nothing in between them or with only two double quotation marks between them, i.e. ,, or , ,. Each record in such a file will always contain the same number of fields. Powerbase does, however, allow nulls to be omitted entirely when creating a file in which case the number of fields per record will vary. quotation marks in CSV files The way in which each record is terminated varies from one system to another. The last field in a record is followed not by a comma but by a line terminator. On RISC OS systems this is the same as in ordinary text files: the linefeed character (LF, ASCII value 10) and on PCs it is usually the carriage-return character (CR, ASCII value 13). You might, however, encounter CSV files in which both these characters are used, i.e. LF CR or CR LF. Powerbase lets you define any character or pair of characters as the record terminator Separators other than commas are sometimes used. The Tab character (ASCII value 9) is often used and such files are called tab-separated values files Powerbase lets you define any character, or even a pair of characters, as the field separator. All such files created by Powerbase will be of type &dfe and display the CSV file icon (although the default filenames offered do differ: CSVfile where the separator really is a comma, TSVfile where it s a TAB and ?SVfile otherwise). In what follows we will, for convenience, refer to them all as CSV files whether or not the separator is a comma. Some applications which accept a CSV file as input expect the first line to contain the names of the fields which comprise the subsequent records, e.g. if each record consists of a name and a four-part address this header record might read: CSV header record CSV line terminator STREET COUNTY POSTCODE Powerbase can both export and import files with such a header. Setting the CSV options Choosing CSV files => Options from the main menu displays the CSV options window which lets you specify all the file characteristics described earlier. Pop-up menus give you a choice of field-separators and record-terminators with space to enter your own if you wish. The first three option switches cause an exported file to have, respectively, the following characteristics when the switch is selected: CSV field separator quotes round non-numeric fields see (1) above a header record specifying the field names as either tags or descriptors see (5) above null fields included see (2) above Note that the field names referred to in a header record are, by default, the of the corresponding Powerbase fields, but may be changed to the descriptors by altering the setting in the Print options window. Exporting data as a CSV file Having set up your options as described above, creating a CSV file is very like printing a list. First highlight the fields to be exported by clicking on each with ADJUST. Remember that the order in which the fields are highlighted is important. Next choose CSV files => Export (Ctrl X) . A window featuring the Query panel appears. Type in a search formula to determine which records are exported. Finally, enter the name of the file and drag the file icon to a filer window. By default the file is saved in PrintJobs as usual, and you may simply click on the Export button or type Return. Experiment with saving CSV files with different settings of the CSV options and then loading the resulting files into Edit to examine them. If you select the Reverse switch on the Query panel the CSV file will be created in reverse order. (See also 3.5.1 field-concatenation option ( see 3.2.2 ) applies. This means that data which occupies separate fields in the Powerbase record need not do so in the exported CSV file. By holding down Shift when selecting the field with ADJUST, the comma (or other separator) which would normally follow is suppressed until you select a field without using Shift. A slightly problematic situation occurs when you concatenate a mixture of numeric and non-numeric fields with the In quotes option selected. In such a case Powerbase will enclose the whole concatenated group within two sets of double quotes. e.g. NAME, Z, M and SYM from the Elements database would be exported, for actinium, as: ACTINIUM 89 227 Ac Spacer see 3.10 in the above instance it is the default setting of one space is used to separate the concatenated fields. 8.3.1 Scrollable lists and CSV files Scrollable fields may appear in a CSV file in two different ways, depending on how their printing option is set in the Print Options window. If As single row is selected the whole list is exported as a single CSV field. The data corresponding to individual rows of the list are separated by semicolons. If As columns is selected then each row of the list is exported as a CSV field. Transferring data from and to scrollable lists in individual records was covered in 2.6.4 Using CSV files import data If you drop a CSV file on the record window of an open database the CSV options window (see ) appears with the title changed to Import CSV file and some additional icons, one of which displays the pathname of the file. Clicking on Import will make Powerbase try to create new database records from the file. If you decide not to do this you should click on Cancel . If you do wish to import the data there are some important consideration which will now be explained. 8.4.1 Ensuring that the correct options are selected If the CSV file originally came from a Powerbase application the settings in the CSV options window need to be exactly the same as they were when the file was exported. The exception to this is the In quotes button which is shaded on import because Powerbase doesn t need it. If the CSV file came from a PC or another RISC OS application you might have to load it into Edit to find out what separator terminator are used. You will also probably need to set the filetype to &dfe (omit the ) as well. CSV options, importance of There are three more option switches at the bottom of the window. One causes each record to be displayed as it is imported. The import process is slower with this turned on but much more informative if you like to know how things are progressing. The second switch, when selected, strips any trailing spaces in the imported data-fields. Some database programs pad all fields to their maximum length by adding spaces to the end of the data where necessary. If you import such a file into Powerbase you will find that the caret will always be at the far right of the field even though visible characters do not fill the field, and some queries won t work properly. Setting the Strip spaces switch before importing the file overcomes the problem. The third option switch determines how Sequence number fields are handled. If the switch is ON imported sequence numbers are ignored and new ones assigned in accordance with the field s sequence number counter. With the switch OFF sequence numbers from the CSV file are imported without alteration. 8.4.2 Directing imported data to the correct fields If no fields on the record window are highlighted (i.e. with ADJUST) and the CSV file does not contain a header record (see ) then the import process proceeds according to the following rules: The first CSV field will be read into the first Powerbase field for which importing is allowed, This means any which can hold text (including External fields) or numbers and also check-boxes Graphics fields, Buttons or fields which are merely labels will be ignored. The next CSV field will be read into the second Powerbase field and so on. If the end of the CSV record is reached before all the relevant fields have been filled ( data underflow ) then the next CSV record will start a new Powerbase record, i.e. the reading won t get out of step. It does not matter, therefore, if the CSV file omits null items at the end of a line If all relevant fields are filled before reaching the end of the CSV record ( data overflow Powerbase ignores the remainder of the line and skips to the beginning of the next CSV record before starting a new Powerbase record. This is also to keep the operation in step. There will be occasions when you don t want to fill the Powerbase fields sequentially as just described. There are two ways of making the process more specific: Highlight the required Powerbase fields with ADJUST before starting the import. Data will then be read only into the highlighted fields, all other fields being ignored. The order in which the fields are filled is the order in which you highlighted them. The rules given above about underflow and overflow of data still apply. Give the file a header record containing the descriptors of required fields in the Powerbase record. There is nothing to prevent you using Edit to add such a header to a CSV file which did not originate from a Powerbase application. The format of the header is illustrated in (5). Importing then occurs just as if those fields were highlighted. The Print options window must reflect whether the descriptors of fields are used. Do not use both a header highlighting. 8.4.3 Importing data from plain text files It was explained in (4) that files created as described above ( can have separators other than a comma. If the file has been created using Powerbase CSV exporting facility it will have been given the file-type &dfe as if it was a true CSV file. If it comes from some other source it is likely to be of type &fff (Text). It can still be imported but caution is needed because there are other circumstances in which a text file might be dropped on the record window . A properly-written script file (see Ch 12 ) would be recognised as such and therefore cause no problem, but any text file dropped onto the appropriate type of External field (i.e. a Text or Text Block field) would become linked to that field instead of being treated like a CSV file. If you are importing data from a plain text file and your record contains fields of the aforementioned types be sure to drop the file on the window background, not on the External field The appropriate window will then appear with the title Import text file You are strongly advised to use proper CSV files if at all possible. You could, in fact, use the filer to set the filetype of such non-standard files to &dfe (omit the ) so that the problem of Powerbase taking the wrong action doesn t arise. Be sure to use Options to set the correct field separator and record terminator though. 8.4.4 What if the imported data won t fit? There are two situations in which this can happen. The database might not contain enough free records to hold all the imported data and so you get a Database full when reading CSV file error. To avoid this either make sure the database is big enough before you start or place a suitable value in the Increment for expansion icon in the Change length window . The latter is accessible from the Utilities submenu of the iconbar menu (see CSV data, truncation of The second situation is where an item is too long for the destined database field. When importing data Powerbase maintains a file called TooBig inside the database PrintJobs directory. Anything which won t fit in the target field is instead written to this file together with information about where it was intended to go and an character is placed in the database field to draw your attention to it. No writable Powerbase field may be longer than 246 characters and if an item of imported data exceeds this a note will be made in the TooBig file advising you to define an External field Text Block or Text) for such data. After completing a CSV import operation it is advisable to look at the TooBig file to see if any remedial action is needed. Using CSV files to modify existing records Most database programs which support CSV import allow it to be used only for creating new records. Powerbase is unusual in that you can use a CSV file to modify existing records This capability should be used with caution since careless use can irrevocably garble a database. There are three relevant radio buttons in the CSV options window. They are called Modify existing, With primary key and With rec. number and their actions are as follows: Modify existing No new records will be created when a CSV file is dropped onto the record window. The existing records will be accessed in the order determined by the current index and the new data will be merged into these records. You should, of course, either use ADJUST to highlight the fields into which the data is to go or place a header in the CSV file specifying the field tags and then turn on the With header switch (see 8.4.2 ). If all the records are modified before the end of the CSV file has been reached a warning message is displayed. Note that it is the s responsibility to ensure that the data in the CSV file is in the correct order since Powerbase has no way of telling which data is destined for which record and can only proceed sequentially. As an additional precaution a warning message is displayed when you choose this option. With primary key This affects both export and import. When you export data with this button selected each record of the CSV file includes the primary key of the Powerbase record. (Try creating a CSV file with and without this option set and compare the files using .) When importing such a file Powerbase will attempt to locate records with the same primary keys as the records in the CSV file. If a matching key is not found a new record will be created to receive the data. If the key does exist then the CSV data goes into the same record, overwriting any data which the target fields already contain. The option is only useful for transferring data between Powerbase databases which have the same primary key field(s) and structure. t forget that Powerbase , by default, allows duplicate primary keys so it is possible for the new data to go into the wrong record. You would be ill-advised to trust this method of import unless you are sure each primary key is unique. With record number This resembles the previous option. It allows data to be exported with the Powerbase record numbers included. Importing into another (or the same) database places the data in records having the same record numbers, again overwriting data which may already be present in the target fields. This option is only useful for transferring data between Powerbase databases in which corresponding records have identical record numbers. One use for it might be to export a set of data, load it into an editor and do some extensive searching-and-replacing, then put the modified data back into the original database. Creating a new, working database from a CSV file There is an option button in the CSV options window called With field data which only becomes active when the With header button is selected. With the switch selected the header record of a saved CSV file contains not only the field names but also the field lengths and types. Again it is suggested that you create a file and look at it in . Each item in the header begins with a number. This is the field length in characters, i.e. the maximum allowed length in the Powerbase record field. Next comes a character which separates the length from the field name. Another separates the field name from the concluding number which determines the field type. (The type numbers may be inspected by looking at the ValStrings file in the Powerbase directory.) A file created in this form is meant to be dropped onto the window of an open database. It should be dropped onto the Powerbase icon on the iconbar when no database is open. Powerbase will attempt to convert the file into a functioning database . All the fields will be ranged on the left of the record window , one beneath another and the primary key will consist of the first four characters of the first field. Don t try to create databases containing Check box , Button or External field s using this method; it s only meant for the most basic type of database in which all fields are of the Editable class. d len Ch 9 Mail-Merging with Impression Ovation Although the title refers to Mail-Merg the process to be described is more accurately termed Data-Merg as it is by no means limited to merging names and addresses into letters or labels; any kind of textual data from a Powerbase application can be merged into any kind of document in Impression Ovation or many other editors and wordprocessors. Mail-merg ing with Ovation Ovation expects data for merging to be supplied as a CSV file. An example file ( Ovation ) is supplied with this version of Powerbase. It was created from the Elements database and is intended to work with the sample Ovation document OmergeTest . Before attempting to create CSV files of your own you are recommended to read the Powerbase documentation on such files (see ). The process for mail-merg ing with Ovation is described below. 9.1.1 Preparing an Ovation document for mail-merg Ovation has a very easy method of specifying where the merged data is to go into the document. Whenever you reach a point where you wish to insert a Powerbase field type Ctrl I . A window headed Merge tag will appear inviting you to enter a number. The number required is the field number in the CSV record (which is necessarily the field number in the Powerbase record). If, for instance, you wish to merge the second field from each line of your CSV file enter 2 and type Return. You will see that [Merge2] has been inserted into the text at the caret. Continue typing, inserting other fields as required. The fields do not need to be in numerical order (you can insert field 2 before you insert field 1) and the same field may be inserted many times. Styles and effects may be applied to the Merge . Load OmergeTest to see how this looks. 9.1.2 Merging the data from Powerbase With OmergeTest loaded, open the main Ovation menu, go to the File submenu and choose Print . Select the Mail merge option button and drag Ovation to the writable icon to the right of the button. The pathname of the file will appear in the icon. Click on and the document will be printed once for each record in the CSV file, merging new data into the document each time. Ovation contains 10 records so if you just want to see the process in action without actually printing 10 sheets of paper set your printer manager to print to a file instead of to the printer itself. If you examine Ovation you will see that each field is enclosed in quotes. This is only necessary of the fields contain literal commas (see ). Look at the first record, for example, and note the comma in (Gr.aktis,aktinos; ray or beam) . Without the quotes Ovation would interpret the comma as a field separator and the merging would get out of step for that record. Mail-merg ing with Impression The following description applies to Impression Publisher and Impression Style . The older Impression is also supported. Powerbase Impression are able to communicate directly with each other without the need for a CSV file. This task is performed by a module called Impulse which Computer Concepts have placed in the public domain to encourage its adoption as a standard means of transferring data between RISC OS applications. The Impulse module is included in the !Powerbase.Resources.Modules directory. 9.2.1 Preparing the Impression document Since the merged data is not being supplied from a pre-generated CSV file Impression needs to request specifically each Powerbase field that it wants to merge into the document. It is probably best to create the document without worrying about the merged data at first, then insert the necessary commands afterwards. Wherever you want data from a Powerbase record to appear in the document proceed as follows: Place the caret at the point where the data is to be inserted. Click MENU over the frame, go to the Utilities submenu ( Misc Impression ) and choose Merge command . A dialogue box will appear. Type the following: :Powerbase GetField NAME This is on the understanding that your Powerbase record contains a field whose tag is NAME and that you want the contents of that field inserting into the Impression document at that point. Note the spaces and the initial colon. These are essential. Click on or type Return. You will see <NAME> appear in the text. Repeat for all the other Powerbase fields to be inserted in the document. All take the form: :Powerbase GetField <tag> and in each case the tag, enclosed in angle brackets, will appear in the text. All the data in a given document must come from the same database. It might be easiest if you open the required database and arrange the desktop so that you can see both the record window and the Impression document. Clicking MENU over a field makes its tag appear in the second item of the main Powerbase menu. Copy it exactly into the Merge command window. (6) Save the the document! 9.2.2 Merging the data Make sure Impression has been by the filer then open the Powerbase database. Drop the Impression document on the record window . The document will be loaded and displayed then Powerbase Data Merge window will appear. (If Impression t already running Powerbase will run it for you.) Clicking on Merge extracts data from either the first record or, if the Merge from displayed record switch is set, the currently-displayed record, and inserts it in the specified places in the Impression document. You can see this happening if the relevant parts of the document are visible. Using the keypad to browse, search or change subfiles or keys now causes the accessed records to be merged into the document instead of being displayed in the record window. If a filter (see 2.3.3 ) is active this will also be obeyed. Each time a record is merged its data overwrites that from the previous record. If your printer is on-line and the printer driver loaded you may print the document with the merged data by clicking on Print . If the Print all switch is selected before Merge is clicked Powerbase will proceed through the database from whichever record you first merged, merging each record in turn (or each matching record if a filter is in use) and printing for each record a copy of the document containing the merged data. Note that when the mouse pointer is moved over the Impression document with the merged data present it changes to a large red and blue cross which means that you cannot edit the document. Clicking SELECT over it has no effect; the caret doesn t appear. If you click on Cancel in the Data merge window the merged data is removed from the document and normal editing is restored. If you want to edit the document with merged data present then click on . Note, however, that if you save the document in this state it will be saved with the specific data you have merged into it rather than with the commands to merge data. Appendix A gives a full description of the Impulse commands or methods which Powerbase understands. Mail-merg ing with other programs If you use neither Impression Ovation you may still be able to use mail-merg ing with your wordprocessor, but you will need to read the manual carefully to find out how. It will almost certainly involve a CSV, or similar, file in conjunction with some method of tagging the document (as described above for Impression Ovation ) to specify where in it the data is to be merged. ck on * This feature does not seem to be supported by Ovation prior to version 1.36 and it is to that version that this information applies. There has been no opportunity to carry out tests on OvationPro so we cannot guarantee that the above method will work with it. OvationPro like Impression , supports Impulse merging and we hope to have this working in a future release of Powerbase * In Impression all merge commands appear in the document simply as <Merge> , i.e. there is nothing to distinguish one merge command from another. To check what a command actually looks like open the Merge command window again and place the caret immediately before the initial . The text of the command will then appear in the writable icon. Ch 10 Utilities All the procedures described in this chapter will be found on the Utilities submenu of the iconbar menu, enabling you to examine and alter the structure of an existing database. Note that, if password s have been set, you need to enter the database with the Manager level password to obtain access to this submenu. Changing the Primary Key primary key of a database is determined when it is created but is not fixed for all time. The primary key choice displays the same dialogue box as was used for creating the primary key in the first place, but with the addition of two radio buttons, whose function is described below. The present key structure is shown. Simply alter it to what you require and click on Create or type Return. The first of the two radio buttons ( Retain subfile structure ) is selected by default, making the records appear in the same subfiles as before. Any subsidiary indices , therefore, will still be valid. If you deselect this radio button with ADJUST (i.e. have neither button selected) then all the records will be placed in the currently selected subfile and you will need to rebuild your subsidiary indices. Selecting the second button ( Restore deleted records ) also places the records in the current subfile, but has the additional effect of recovering deleted records, i.e. records which are still lurking in the Database but aren t in the primary key index . You would need to use this option when the primary key index is lost or corrupted. If the PrimaryKey file is actually missing both radio buttons are shaded in order to enforce this mode. Note that you cannot recover deleted records if the Blank record on deletion switch is set in the Preferences window: the data has really gone for good in this case. Adjusting the record format Minor changes can be made without rebuilding the database. Choose Adjust format and the blank record window with the blue grid will be displayed. Clicking MENU over this brings up the same menu as you used to create the screen originally, but the Field definition window has some options shaded. Thus you can t delete fields, add new ones or alter the data length s, but you can re-position fields, change descriptors and , and alter the visible lengths of data fields. You can also change the field type within its class , e.g. an Alphanumeric field could be changed to a Numeric one since both belong to the Editable class, or you could change a 2-state Check-box to a 3-state one, but you couldn t change either into a Calculated field. When you have finished making the changes choose Quit design from the menu. record format, minor adjustments to Changing the record format This is more drastic, but it is not always possible to foresee future needs and you may have to introduce a new field or lengthen an existing one. That involves rebuilding the whole database. Powerbase actually builds a completely new database under another name, leaving your original database unchanged in case anything goes wrong, so make sure you have enough disc space available. Choose New record format . The Reformat database window will appear. Several options are now available: Enter the new database name and drag the database sprite to a filer window. You will then be offered the record layout for editing, as when creating a new database. Make the required modifications then choose Quit design from the menu. database, reformatting If you already have a file in the new format drop it onto the Reformat window where its name will appear. Enter the new database name and drag the sprite to a filer window. Create a new database shell (which must have a different name from the one to be reformatted) and copy your existing Form file into it. Open this database , edit the layout and save the file. Open the database, call up the Reformat window and drop the new one onto it. Its pathname will appear in the window. Finally click on Reformat . This was the method used in versions of Powerbase prior to 7.52 and, although it still works with the latest version, it should be regarded as obsolete. database, adding new fields new fields, adding to database Data from a field in the old database will be copied to a field in the new one with the same tag. The new database must therefore retain the same as the old for data which is to be common to both. A tag present in the old database but not in the new is assumed to refer to a deleted field and the associated data is not transferred. A field present in both databases but with a shorter defined length in the new one will have its data truncated if necessary. A tag present in the new database but not in the old is assumed to be a new field and will be left blank. Before proceeding Powerbase will inform you of any likely data loss due to deleted or shortened fields and ask you if you wish to proceed. When the process is complete you will be left with the new database open. Links to validation tables, calculation formulae, and indices are preserved unless a relevant field was omitted from the new record. Merging two databases Two databases may be merged provided they have identical record structures. With the first database open, select Merge database and drag the second database to the displayed window. The pathnames of both databases can now be seen. The pathname of the open database is duplicated in a writable icon labelled Save as . If you accept this default the data from the second database will be merged into the open one. You can, however, change this name and drag the database icon to a directory window. The merged data will then go into a completely new database leaving both source databases as they were. When you click on Merge database Powerbase will check that the two record formats are identical and report an error if they aren t. An option switch determines the action taken with regard to Sequence number fields (see 8.4.1 Two radio buttons are also present, as in the dialogue box for rebuilding the primary key , and their functions are essentially the same (see ). Having the first button selected merges records into the same subfiles as those they occupied in the second database. Having neither selected merges records into the current subfile of the first database, regardless of where they are in the second database. Having the second button selected does the same, but also imports records which were deleted in the second database. Changing the Database Length The number of available records in a database may be increased or decreased. Change length leads to a window with two writable icons. The first specifies the new database length, the other determines the number of records by which the database will be lengthened each time it becomes full. If this value is zero no automatic lengthening occurs; a warning is displayed instead. You will only be allowed to shorten a database if the surplus records have never been used or have been blanked on deletion. Otherwise you can only get rid of them by exporting all the current records as a subset (see 13.2) Inspecting and balancing index trees Print index lets you examine the structure of the current index to find out how many are present in each level. The ideal numbers in a perfectly- balance index tree are 1, 2, 4, 8, 16, 32, 64, 128 etc. (i.e. powers of 2, beginning with 2 .) A submenu gives you the option of printing the actual , positioned according to the level they occupy in the tree, or printing only the total number of nodes in each level. The data printed is for all subfiles of the current key, but there is an option to restrict it to the current subfile. There are also two alternatives for the layout of the complete tree. Output is to a screen window from which it may be saved in the same way as any other report. (If you have printed the themselves then double-clicking with SELECT on any key will call up the associated database record.) If the tree is very un balance d and, especially, if there is an enormous number of levels with only 1 or 2 in each, you are advised to balance it using the Balance index choice . It is also possible to make Powerbase balance the index automatically at regular intervals. To turn on auto-balancing choose Preferences from the iconbar menu. Select the Balance every <n> records switch, placing your chosen value of in the writable icon provided, then click on Accept Auto-balancing is most likely to be useful when a large number of records are being entered in primary-key order. This will happen if you are entering data from a lot of forms in alphabetical order of name where name is the primary key . It is also very likely to happen when importing CSV files as these are often ordered according to the data item which becomes the primary key field of the database record. Finding duplicate primary keys Wherever possible a primary key should be chosen so as to be unique. Where duplication of the primary key might occur the designer of the database can decide either to allow or forbid it (see 11.2.1 ). If duplicate are permitted it is sometimes useful to have a list of them. Such a list is created by Find duplicates and may be saved as a text file. ecord * A poor choice of key, with many duplications of just a few values, inevitably leads to a tree with many levels in which each level is sparsely populated. An attempt to balance the tree under such circumstances may give very little improvement. Ch 11 Passwords and related matters Levels of protection Powerbase provides three levels of password protection . None of the sample databases uses password s, so you may examine the password -setting dialogue box which is accessed by choosing password from the Miscellaneous submenu. If password s have been defined then an attempt to open the database leads to a request for a password . This is not readable as you type it in; all the characters are replaced by hyphens. If Powerbase does not recognize the supplied password it beeps and prints an error message in red then prompts for the password to be re-entered. After three failed attempts Powerbase will shut down. The lowest password level is . It is intended to let users examine the database but otherwise keep them out of mischief. A user opening a database with the Read password will be allowed to browse, search and print lists (unless debarred as described in ), but not much else. In fact it will be found impossible to place the caret in a field. The function keys won t work, many of the buttons on the keypad will be inactive and some menu items will be shaded. In particular, the whole of the Miscellaneous submenu is unavailable, thus preventing the user from getting at the other password password levels The next level, Read/write , allows more operations. Everything on the keypad is allowed, including altering, creating and deleting records. You may create indices and create and edit validation table s. What you cannot do is change password s or alter the size and structure of the database by means of options on the iconbar Utilities menu. The top level is Manager . At this level you are allowed to set and alter password s, as well as use all the iconbar menu options. Passwords may be up to 10 characters long and are case-specific. Note that when you are setting the two lower ones they can be read but the Manager password appears even here as a row of hyphens. Take care not to forget it! If no password s at all are set you have full Manager rights so you may ignore the password facility altogether if you are the only user of the database. If you decide to use password s you might not wish to use all three levels of protection and don t need to do so. You may set a Manager level password only, leaving the other two blank. When you attempt to open the database in such circumstances the password -entry window appears because Powerbase t read your mind and doesn t know whether you re the Manager and wish to enter your password or whether you re a lower-level user being given limited access without needing a password . If the latter you would simply click on Open or type Return, thus entering a null password . This would give you Write access. If you set the Manager Read/write password s then entering a null password would give Read access. It obviously makes no sense to set a low-level password and leave a higher level one blank! Powerbase prevents you from doing this in the following way. If there is a Read password but no Write password Powerbase makes the and Write passwords the same. Similarly, if there is a Write password and no Manager password these two are made the same. This means that setting only the Read password makes all three identical. You are then admitted to Manager level when you enter this password - otherwise you could never get Manager level access again! The rule is that when two or more password s are identical you are always given the higher level access when using that password 11.1.1 Individual I.D.s and passwords It is possible to assign a user an I.D. and a personal password , thus giving the data even greater protection since access to a specific database can be limited to those people who appear on an access-control list . It does not seem appropriate, however, to describe the workings of an access-control list in a manual for general consumption! Anyone wishing to set up a secure database for use by a limited number of specified users is welcome to write for information to Powerbase Support, explaining their requirements. If a database is protected by an access-control list the Access window which appears when you attempt to open the database will display two writable icons instead of one. The first is for the user s I.D., the second is for the password . Enter both and click on . A an error in either the I.D. or the password will result in access being denied without informing the user which input contained the error. Options selected from the password -setting window As well as the icons for the password s you will see eight option switches which may be used to enable or disable certain features of Powerbase so that you can customise your database to some extent. They are placed here so that they may only be altered by someone who has Manager rights as that is the only access level which allows you to open this window. All switches except the last ( Log changes ) are ON by default so that all the named features are enabled. If you deselect any of these switches the feature concerned will cease to operate when you click on and will remain inoperative every time the database is opened unless you set the switch to re-enable it. keypad, disabling function keys, disabling main menu, disabling iconbar menu, disabling The first four switches determine the availability of the keypad , the equivalent function keys , the main menu and the iconbar menu. All these features are enabled by default but you may disable some or all of them in order to restrict the actions of other users of the database. This subject is treated fully in Ch 14 The fifth switch determines whether or not the exporting of CSV files and subset databases is allowed and the sixth does the same with regard to report printing reporting, disabling disabling functionality 11.2.1 Duplicate primary keys The seventh switch determines whether the database is allowed to have duplicated primary keys or not. As mentioned elsewhere (see 4.5.2 ) the primary key should, ideally, be unique for each record. Sometimes the very nature of the data will make it so but, if this is not the case, you can enforce primary key uniqueness by deselecting this switch. Any attempt to enter a record which would have a primary key identical to one which already exists will then result in an error message and the new record will not be placed in the file. If you decide to allow duplicate primary keys (and this is the default, remember) you might still want to be warned that such a key is about to be created and a switch on the Preferences window allows you to enable or disable this feature. You can also make Powerbase print a list of duplicate primary keys (see The above refers only to the primary . Other, subsidiary which are generated when you create an index on some other field (see ) are subject to these restrictions and may be repeated many times. Logging database changes Selecting the last option switch ( Log changes ) and clicking on opens a log inside the database directory. Powerbase will then record when the database is opened and closed and details of changes made to the records. The following changes are logged, details in brackets showing what is recorded in the log file creation of a new record (record number & primary key ) deletion of a record (record number & primary key) shifting a record to another subfile (record no., primary key, old and new subfiles changing a field in a record (record no., primary key, old and new contents) changing a field in a group of records (change, subfile and search formula) creating an index (name of new index ) balancing an index (name of index concerned) changing the primary key (former structure of key) reformatting the database (fact only no details) merging data from another database (source of merged records) importing CSV-type data (source of imported data) As long as the Log changes switch remains selected the log file will be opened whenever the database is opened. The title of the database is recorded and the password level at which entry was achieved. This takes the form of a number with the following meaning: password required, 1 read only, 2 read/write, 3 manager The date and time are also recorded. When you close the database the date and time are recorded again and the log file closed. The log file is a plain text file and is always called . Although the default location for the file is inside the database directory you may start a log somewhere else by dragging the small text-file icon next to the Log changes switch to a directory display. When you release the mouse button the password window is closed just as if you d clicked and logging is enabled. An advantage of this method is that you may perform the same drag to the same directory from many different databases and have them all use the same log. The !Scrap application is a possible location for a general log of this kind. f the Ch 12 Script Files Powerbase incorporates a script language which lets you control many features from a script file . Using script files you can automate jobs which need to be done frequently, print jobs being the most obvious example. Script files are plain text files (created with StrongEd etc.) and are executed by dragging them to the Powerbase record window. All commands in a script file must be in upper-case, preceded by , and may be followed by parameters (which are not case-sensitive), separated from the command by a single space. A complete list of commands follows. Parameters in square brackets are optional. Where commands simply control the status of options switches (e.g. !CASE, !UPPER) the parameter is either ON or OFF. !SCRIPT Since scripts are just text files there has to be a way for Powerbase to recognise them. All script files must therefore start with a line beginning with !SCRIPT. Prior to v.8 ! SCRIPT POWERBASE was required; you may still add POWERBASE but it is no longer necessary. You may use ! SCRIPT END as the last line of the file but this is also optional since the script will terminate anyway when the end of the file is reached. You can make the command chain another script file by following it with the name of the new file, i.e. by using the form ! SCRIPT <filename>. If you just give a leafname Powerbase will look for it in the database PrintRes directory. If it is stored anywhere else the full pathname must be supplied. This rule applies to all script commands (e.g. !DELETE, !INCLUDE) which can take a filename as a parameter. When a script file is dropped onto the record window its name will be displayed with a request for confirmation that the file is to be executed. To suppress this action use !SCRIPT QUIET as the first line. !COMMENT <string> may be used to insert comments in a script file. The rest of the line is ignored. !MESSAGE <string> will display a user-defined message in a small window to keep the user informed of what s happening during execution of a script. !MESSAGE on its own closes the message window. !SELECTION is used to select fields for inclusion in a report. If a filename is given as a parameter Powerbase looks for a print Selection file of that name and loads it, just as if you had double-clicked on it or dragged it to the record window. (See !SCRIPT, above, for the interpretation of filenames.) Alternatively, you may supply a comma-separated list of the tags of the required fields and may include the record number, key and subfile number by placing RECORD, KEY and SUBFILE, respectively, in the list. If you supply no parameter at all the current selection will be cleared. !PRINTOPTS <filename> loads a Print options file, setting the options just as if the the file had been double-clicked or dragged to the record window. Without a filename it sets the default options. !SUBFILES <n>[,<n1>,<n2>...] selects the subfile(s) which will be searched in order to create a report. !CASE turns the Case switch on the Query panel ON and OFF. !QUERY [filename\]< search formula is used to print a report. It returns the same result as entering a search formula in the Match window and clicking Print . If you first issue a !DESTINATION FILE command you can use a list of !QUERY commands to create a batch of reports as text files. If you omit the optional filename each file will be created in PrintJobs using the search formula (or part of it) as the filename. If you supply a name containing $ it is assumed to be a full pathname and will be used to create the file. Any other name causes the file to be created in PrintJobs or in a subdirectory (which must exist) of Print Jobs. Note the backslash between the filename and search formula; this tells Powerbase where the one ends and the other begins. !CASE also affects the behaviour of this command if issued beforehand. !SAVE <filename> may be used to save a file which has been created in a window. It is only useful after a !QUERY command for which the destination is Window. [filename\]<search formula> is similar to !QUERY but produces a CSV file instead. The CSV option settings are observed. A prior !DESTINATION command is unnecessary in this case. !INCLUDE !EXCLUDE !CLEAR may conveniently be treated together. They have the same effects as the controls on the Mark pane attached to the bottom of the record window. !INCLUDE <key>[,<key>,<key>...] has exactly the same effect as ticking the check box on the Mark pane for records with the associated primary keys. !EXCLUDE (same syntax) has the same effect as marking with a cross, which is what happens when the alternative option from the pane s pop-up menu is chosen. !CLEAR clears all marks. As an alternative to a comma-separated list of keys you may supply a filename. The file should be a plain text file with one primary key on each line. See !SCRIPT for the interpretation of filenames. !FORMAT <string> where <string> is Horizontal Vertical , Label or Table sets the print format. If any other parameter is used, or !FORMAT is issued by itself, the Horizontal format is used. !DESTINATION <string> where <string> is Window, File or Printer sets the destination for report printing (see ). If File is used the report is saved in PrintJobs unless a subsequent !QUERY command supplies an alternative pathname (see above). !EXPAND selects the the switch which causes fields to be expanded by reference to a linked validation table . !EXPAND OFF (or just !EXPAND by itself) deselects the switch. !DATE turns date and time stamping ON and OFF. !UPPER forces upper-case printing ON, normal upper/lower case printing otherwise. !HEADER, !FOOTER determine whether header/footer information (see ) is printed on reports. !FIRST when followed by ON causes the header to appear on the first page only. (Footer information only appears on the final page anyway.) !SHRINK determines whether surplus white space is removed from between the columns of reports in Horizontal format. !HEADINGS causes descriptors to be used as field identifiers in a report. !HEADINGS T, or without any parameter, causes to be used. !TITLE <string> uses <string> as a title on subsequently printed reports. !PAGE sets the page length for reports to lines. 64 is probably correct for A4 (but see !SPACER <string> specifies the string to be used to separate columns of printed data in reports using Horizontal Table format (see !TEXTWIDTH specifies the maximum width in characters for Memo and Text block fields in reports. !ORIENTATION <LANDSCAPE, SIDEWAYS> changes the paper orientation for hard-copy printing from the default: !ORIENTATION PORTRAIT or UPRIGHT turns it back again. !HEADERFONT !BODYFONT <fontname> set the fonts for headers and footers and for body text in hard-copy printing. Names should be of the form Homerton.Medium, Trinity.Bold.Italic etc. The font must, of course, be known to the font-manager. !FONTSIZE sets the size, in points, of the fonts used for hard-copy printing. !LMARGIN !RMARGIN !TMARGIN !BMARGIN set the print margins for hard-copy printing. The units default to mm unless specified by appending mm, in or pt to the number. !PMARGINS cancels the margin settings in the Printer Setup window and uses the printer driver s default margins instead. !LINESPACE sets the spacing between the baseline of one line of printed text and the next as a percentage of the font size. The default is 120%. For Table format 150% looks neater. !TABLE <columns>,<width>,<rows> sets up the number and width of blank columns, and the number of blank rows at the end of the report, in Table format. Column width is in mm but may be changed as for margins (see above). You need not specify all the parameters; current values will be used if any are omitted. You may, for example, specify 6 blank rows and nothing else by using !LABEL ,,6. Note the commas. !LABEL <n>,<w>,<h>,<str1>,<str2>,<s>,<su>,<bl>,ON/OFF determines the setup for label-printing where: n=number of labels across the page, w and h are the width and height of label (units may be specified as for margins and column widths), str1 and str2 are the optional fixed first and last lines and s=number of label at which printing on first page is to start. su and bl are field tags. If field bl is blank then field su will be printed instead. The final parameter specifies whether the primary key is to be printed on the label. As with !TABLE you need only specify the values you wish to change, but watch those commas! !COPIES specifies the number of copies to be printed !SORT specifies a column on which the report is to be sorted before printing. The tag of a field included in the report may be specified instead of a column number. The !SORT must appear before !QUERY in the script file. It cannot be used when the output destination is File. !IMPRESSION is used in conjunction with !QUERY to generate a report in text-file format, allows you to insert Impression commands at the start of the text-file. A script may, for example, execute a series of !QUERY commands and the resulting files are to be all selected together and dragged into an Impression document. To make each file go into a new frame you need to make each begin with {nextframe}. This can be achieved by placing the script command !IMPRESSION {nextframe} before the first !QUERY. You do not, however, want {nextframe} to begin the first file created, or the Impression document will have a blank frame on its first page. To suppress the effect on the first file use: !IMPRESSION {nextframe} Not first. There must be a space between the and the , but the Not first string isn t case sensitive. !DELETE <key>[,<key>,<key>...] deletes record(s) having the supplied primary key(s). As an alternative to the key or key-list you may supply the name of a text-file containing the required keys, one to a line. The filename will be interpreted as described above for !SCRIPT <filename>. The command can delete records in any or all of the six subfiles, not just the current subfile !INSERT is the counterpart of !DELETE. Useful in similar circumstances. In this case the command must occupy a line by itself and be followed by the record to insert, one field to a line. It is the user s responsibility to ensure that the lines are not too long for the database fields into which they are placed and that the number of lines following each !INSERT is the same as the number of fields in the record. Don t forget to leave blank lines for empty fields! Records will be inserted into the currently-selected subfile. !MOVE has the same syntax as !DELETE but moves records from their present subfile to the next in sequence, regardless of the currently-displayed subfile; e.g. a record in subfile 2 will be moved to subfile 3. !CHANGE <field tag>,<old contents>,<new contents>[,<search formula>] allows global changes to be made. The command functions like the Global changes menu choice (see 2.5.5 !OBEY <string> can be made to do anything which a *command can do. e.g. !OBEY Delete <filename> will delete the named file. !OBEY <filename> will attempt to *Run the file. By supplying the pathname of a Powerbase database you can close the existing database and open a new one. !KEY <index name> where the specified index name appears on the menu of loaded indexes will make that the currently active index. If no parameter is given the primary key index is selected. !INDEX <tag>[,<tag>,<tag>...] constructs an index on the field whose tag is first in the list and makes it the currently active index. If further tags are supplied each is treated in exactly the same way. Using the Elements database, for example, the command !INDEX Z,GP would index first on the Atomic number (Z), then on the Group field (GP) with the Z index active . The database is left with GP as the active index and a report of the whole database shows the records ordered by Group but, within each group, the records are in order of Atomic number; a sort within a sort. The whole field (with spaces ignored) is used as the key unless you specify a shorter key by placing a number before the tag, e.g. 5NAME for the leftmost five letters. Powerbase remembers the names of the indices created by the most recent !INDEX command and will delete them if you issue !INDEX with no parameters. !FILTER <search formula> has the same effect as entering a search formula in the Filter window. The Filter window isn t opened but the Filter switch on the keypad (or on the record screen if there s a Filter switch there) is turned on and the first matching record is displayed. !FILTER on its own cancels the filter. !STARTAT <tag> sets the field at which editing begins and places the caret in that field. If no parameter is given, or if the designated field doesn t exist or isn t writable, editing starts at the first writable field. Requesting user input from script files This is best illustrated with an example. !FILTER GP=1 will use GP=1 as a search formula. However, !FILTER Search formula? behaves quite differently. A small window pops up saying Search formula? with a writable icon into which you enter the formula then click or type Return. This applies to all script commands. Instead of putting the parameter itself in the script, supply a prompt followed by a question mark and the parameter and will be requested and used with the command. Why would you want to do this? You might use a script to print a report in Table format but not always want the same number of blank columns and rows. You can request these with !TABLE Columns?,,Extra rows?,. Note the double comma (column width is not changed) and the comma after Powerbase checks the parameter string for a concluding which, if found, causes the whole string to be treated as a prompt. This is fine for commands like !TITLE which take only one parameter but those which take multiple parameters need the concluding comma. FILTE * DDF: Document Description Format; the means by which styles and effects are encoded within an Impression document. Ch 13 Subset databases Subset databases are fully-working Powerbase databases with exactly the same structure as the main database but containing only a selection of its records. Creating a subset Creating a subset is extremely simple. Choose Export subset from the main menu and you will see a window strongly resembling the one used to export CSV files . It features our old friend the Query panel into which you type a search formula which determines the records to be exported as a subset It is then only necessary to enter a suitable filename and drag the database icon to a filer window. The default pathname creates the new database as Subset PrintJobs. s probably not where you want it, but it is at least a familiar location from which you can extract the subset database later. You can accept this default by just clicking on Export or typing Return. Records are exported from the currently-selected subfile and will be placed in the corresponding subfile of the subset database. On opening the new database you will find that it functions exactly like the original but contains only the selected records. It is made just large enough to contain the selected records so you will need to increase its length (see ) if you intend to add any further records. Using a subset to shorten a database Database, shortening using subset A database which has undergone a lot of deletions might have blank records scattered at random throughout its Database file and unused keys scattered at random throughout its indices. If a database has 100 available records of which only 50 are in use you might want to get rid of the surplus records but aren t able to do so by the simple shortening procedure described in Ch 10.5 because that only allows you to lop off the of the database. The amount by which the database can be shortened is often much less than the number of in-use records would suggest: in extreme cases you might not be able to shorten it at all by this method. (Don t be afraid to try, however. Powerbase t let you butcher your database; it just tells you you can t do it.) The solution to the problem is to export the records as a subset. Simply follow the instructions in but don t type anything into the query panel. The resulting subset will contain the 50 in-use records and no extras at all. There is a snag, unfortunately. The Export subset feature operates only on the current subfile. If your database uses more than one subfile things are a little more difficult. You could accumulate all the records in subfile 0 (see 2.5.6 ), export the subset then move the records into the required subfiles. Or you could export each subfile as a separate subset (the records in the subsets will be in the same subfiles as in the original database) and then merge the resulting subsets into one new database (see Ch 14 Customising Powerbase This chapter describes all the things you can do to influence the way Powerbase looks and functions. Overall control of the database Powerbase database is normally controlled in four ways: mouse-clicks on the keypad keypad, disabling function keys, disabling main menu, disabling iconbar menu, disabling keystroke s which mimic the actions of the keypad choices from the main menu choices from the iconbar menu disabling functionality Each of these can be disabled by deselecting one of the option switches on the Password window (see ). The main reason why you might want to do this is to provide limited facilities to someone else who is using your database. It doesn t, on the face of it, make a lot of sense to disable both the keypad the keystroke -equivalents as no browsing or editing would then appear to be possible. You can, however, place some or all of the keypad buttons on the record window itself (see 4.2.8 ) thus allowing you to choose what functions you want to allow access to. You might, for example, wish to provide only the Next record Previous record and Search buttons. Define these as Button field s at the time the record is being designed then disable the keypad and keystroke equivalents. Other switches in the Password window enable you to stop the user printing reports or exporting data as CSV files subset Defining the function keys Any button on the keypad may have its action duplicated by one of the F1-F11, with or without the use of Shift or Ctrl. Point at the button to which you wish to assign a key and click MENU to display the keypad menu. Assign leads to a window which shows the present key-assignment, if any. Use the icons to select the required function key and, if desired, select one of the switches for Shift and Ctrl. Click on the Assign button and you will now find that the assigned key exactly mimics the keypad button even to the extent of actually pressing the button when you type the key. function keys, assigning One important constraint should be noted. Whatever key you assign to the Searc h button Powerbase will automatically assign the shifted version of this key to the search all subfiles function (see 2.3.1 ). For this reason you should assign an unshifted key to Find and avoid assigning the shifted version to any other keypad button. Similarly, assigning a key to the Add record button automatically assigns the shifted key to the Copy record function. Record, copying Records, searching all subfiles for You may save your key definitions by choosing Save choices from the keypad menu and you will find them operative next time you use Powerbase . Note that your definitions work with the databases you use; there is no facility for saving separate assignments for each database. The default keystroke s (together with some which duplicate non- keypad actions) are listed in Appendix B . If they have been redefined you may restore the defaults by choosing Defaults from the keypad menu. If you wish to the redefined keystroke s permanently you should restore the defaults as described then Save choices List keys on the keypad menu displays all the key definitions (including those for non- keypad functions) in a window from which the information may be saved as a text file. Function keys may also be used to enter data into record fields or writable icons in dialogue boxes. The actual programming of the keys is done at the command line which is accessed by pressing f12. This takes you temporarily out of the desktop and gives you a prompt at the bottom of the screen. Suppose you want to program f3 to enter the word Powerbase . Type, at the star prompt: Key3 Powerbase and press Return twice to return to the desktop. The string assigned to the key may be entered by typing Ctrl Shift F3 . Any of the keys F1-F9 may be programmed and the string is always entered by typing the key with Ctrl and Shift both held down. function keys, storing strings in CSV options It was explained in how to access the CSV options window and what the pop-up menus and option switches are for. The buttons Save choices as default in database Load default and Cancel work in a similar way to that described above for the Print options window with one notable difference. No Save box appears when you Save choices in database ; the file is saved as a plain text file called CSVoptions This more limited behaviour has been chosen because, although you might want several Print options files for different jobs, it is unlikely that you will have a use for several CSV options files. Preferences Several previous references have been made to the Preferences window which is opened from the iconbar menu. A full de script ion of the choices offered in this window follows. 14.4.1 Separators in date and time field Date separator Time separator By default Date fields have the format dd-mm-yy or dd-mm-yyyy, i.e. they use a hyphen as a separator. Time fields have the form hh:mm:ss, i.e the separator is a colon. These defaults may be changed by entering your preferred separators in the two writable icons in the Separators section at the top of the Preferences window and clicking Accept. Note that when entering dates or times into these field types you may actually type any non-numeric character you wish as a separator. On moving to another field Powerbase will re-format the date or time to use the separator specified in Preferences. CSV file, edits saved as 14.4.2 Wild-cards The use of wild-card characters is described in 2.5.5 and 3.5.3 . By default represents a single character and a group of characters which need not be matched. You may change either or both wild-card characters if you wish in the same way as described above for date and time separators. 14.4.3 Option switches Record edits as CSV file when ON causes a CSV file called NewData to be created in PrintJobs . New and edited records are saved in this file which may then be used to enter data into another database. Launch new copy when ON causes another copy of Powerbase to be loaded when you double-click on a database, instead of closing the current database. This can be useful if you want to access several databases at once, as one copy of Powerbase only supports the use of a single database. If there is a dormant copy of Powerbase on the iconbar (i.e. one with No data under its icon) then double-clicking a database sends it to that copy rather than launching a new one. With the switch OFF double-clicking on a database closes down an already-open database before opening the new one. Even then you can load multiple copies of Powerbase by double-clicking on Powerbase itself and then open a different database in each by dragging the databases to the icons. Case-sensitive queries You can choose the default state of the corresponding switch on the Query panel by setting this switch as required and saving the Preferences. Recalculate on opening affects Computed field s (see and ) whose associated formulae make use of the system variable TIME$ and also those Computed fields which are analogous to Stamp fields (see 4.2.10 ). With the switch OFF the contents of such fields are recalculated only when a record is displayed. This might not be good enough where, for example, TIME$ is used in a User function (see ) to keep track of people s current ages. Selecting the switch causes Powerbase to scan the whole file and update such fields when the database is first opened. Blank record on deletion If this switch is OFF then deleting a record from the database merely removes all references to its key(s) from the index (es), leaving the actual record data untouched in the Database . Although such deleted records will be overwritten when new records are entered, the situation may be deemed unsatisfactory from a security point of view since the deleted records can be examined by simply loading Database into . If the switch is ON deletion causes the relevant record to be over-written with a blank record. Keep descriptor with data alters the behaviour of fields as they are dragged around the screen when designing the record layout. With the switch OFF you may drag the data field and the descriptor independently of one another. With the switch ON this is true only of the descriptor ; if you drag the data field the descriptor jumps to its normal position to the left of the data field as soon as you release the mouse button. Validate input turns data validation (see ) ON and OFF. If you ever find yourself unable to escape from a field linked to a validation table you can bring up the Preferences window, deselect this switch and try again. Shift-F9 also will toggle validation ON and OFF. If the keypad is visible you will see the List values button become shaded when validation is disabled. validation, turning ON/OFF Display linked table data Every time the caret enters a field with a link to a validation table a window showing data from the relevant validation table row appears to the right of the field if the switch is ON. This is either useful or infuriating, depending on your point of view. If turned OFF you can still call up the window for a specific field by double-clicking on the field with SELECT (see Warn of external deletion Since deleting data from an External field (see 2.6.3 ) involves the deletion of an actual file you will probably appreciate being warned when you are about to do it. With this switch ON you will be given a warning and the opportunity to change your mind. External fields, warn of deletion of Warn of duplicates The creation of records with identical primary keys can be prohibited if so desired (see 11.2.1 ). Normally, however, they are permitted. You may, if you wish, turn this switch ON to issue a warning that such a record is about to be created. duplicate keys, warning of Default action on Return According to the Style Guide for RISC OS typing Return anywhere inside a dialogue box ought to have the same effect as clicking on the default action button (that s the one with the yellow channel border) regardless of which writable icon the caret is in. If that s what you want then turn this switch ON. Many users (including the author!) it and prefer the default action to take place only if the caret is in the last writable icon when Return is typed. Hence the default setting, which is OFF. Strip leading spaces is ON by default. Spaces at the beginning of data fields will be stripped before the record is written to the file. Turn the switch OFF if you actually want spaces in this position Strip trailing spaces should normally be left ON to avoid unwanted spaces at the right of data fields. It is easy to press the space-bar by accident when entering data quickly and you might not notice that a field has one or more spaces between the end of the last word and the caret. Balance every <n> records The need to balance indices and the use of this option is explained in Remember place in subfile With this switch OFF a change of subfile leads to the display of the first record in the new subfile. If you are doing a lot of hopping back and forth between subfiles to inspect specific records you might want to select the switch. It is then just as if you had bookmarks in separate chapters of a book; when you return to a subfile you have previously visited you will see the same record you were viewing when you left that subfile. 14.4.4 Save indices (Default: Manual) Indices are always saved whenever you close a database or quit Powerbase and only fear of power cuts or system failure (which includes some kind person switching off your computer!) need make you concerned about saving them yourself. With the Manual setting nothing happens during normal working unless you click Force update on the keypad . Selecting Automatic Warn only allows you to type in a suitable time interval (default = 10 min) at which Powerbase will either issue a warning to save your indices (using Force update ) or actually save them for you. 14.4.5 Start editing at Editing, choosing starting field When a record is displayed the caret is placed, by default, in the first editable field. Editing may be made to start at a different field by choosing Start editing from the Field submenu (see 2.5.4 ) or, alternatively, by placing the caret in the required field and typing Ctrl S . The tag of the relevant field is stored in this writable icon in the Preferences window and can be saved along with the other preferences. 14.4.6 Application for Impulse data-merg As noted elsewhere ( Powerbase can merge directly into a suitably-formatted Impression document by simply dropping the document icon onto the record window . The dialogue box which then appears contains a writable icon which shows the application with which Powerbase will merge. This name is, by default, Impression , but may be altered by users for their own purposes. An application which will receive data from Powerbase in this way must make use of the Impulse II protocols as defined by Computer Concepts. Even if you are using Style Publisher the name to which these applications respond is still Impression 14.4.7 Save choices This section of the window works exactly as it does for CSV options (see ) as do the Load default and Cancel buttons. Colours of key field s and table- linked fields These may be selected in a window reached by choosing Colours from the Miscellaneous submenu. The following default colours are used: Colours used to identify key fields Colour used to identify linked fields Foreground Background Descriptor key field (inactive) dark blue light grey Descriptor key field (active) light grey Data icon of primary key field (s) black pale yellow Data icon of field linked to validation table dark green white Data icon of mandatory field white Clicking with SELECT on the descriptor and data icons within this window makes either the foreground or background (depending on the setting of the two radio buttons) cycle through the available colours. Clicking with ADJUST cycles through the colours in the opposite direction. The effect of the changes can be seen immediately and Save choices allows you to save them in the database as a file called Colours . Different databases can have different colours. Defaults enables you to return the colours to what they were before by loading Powerbase Colours file, but you can overwrite this too if you wish by selecting in Powerbase before clicking Save choices. The Close button merely closes the window. Config files Inside the Powerbase application directory is a sub-directory called Resources The file Config , which will be found there, determines certain aspects of the program s operation which will seldom need changing. Changes made to Config take effect only when Powerbase is next loaded. Each attribute is identified by a token word and there is a comment line describing each. The attributes in Config are as follows: Fields Maximum number of fields per record (default = 100, maximum = 127) Keys Maximum number of subsidiary indices (default = 10) Tabs Maximum number of validation tables (default = 10) Cols Maximum number of columns in a validation table (default = 20) Scrolls Maximum number of scrollable lists (default=5) BTime Number of seconds for which start-up banner (for a registered copy) is displayed (default = 2) LeftOpen Whether submenus off the iconbar menu open on the left instead of on the right as normal. This avoids the overlapping submenus which can occur but many users hate it! (Default: NO) BackGnd Background colour of record window. Default=1 (pale grey). Upper Whether password entry is forced to uppercase. (Default: NO) DirOpts Display option for directories opened by buttons on the record window. Default is -si (small icons). This may be changed to -li or -fi (large icons and full information respectively). The options to sort by name, type, date and size (-sn, -st, -sd, -ss) may also be added after a space. ExtFiles (default=NEW) Method used for storing Text, Sprite and Draw files which are linked to External fields. You should normally leave this set at the default (NEW). A description of the old and new methods is given below ( 14.6.1 Query Default query method. Default: SF (Search Formula), alternative: QBE (Query-By-Example). PathLen Maximum pathlength for Remote filer objects (default 255; max. prior to RISC OS 4) FontAdj Whether width of data fields takes account of desktop font (default: YES) Multi Whether Powerbase multi-tasks when printing lists, indexing etc. (default: NO) Multitasking Markpane Whether pane for marking records for printing etc. appears attached to record window. (default: YES). NameLen Maximum filename (i.e. leafname) length (default: 10). If you regularly use something like LongFiles , or if you have RISC OS 4, you might want to increase this value but beware if you give someone one of your databases containing saved files with names longer than 10 characters! ButtonAtts Attributes of Directory and Run file buttons when used on record window (see 4.2.11 ). The default (42) is the width in pixels (not OS units). The sprite will be vertically, as well as horizontally, centred on the button unless you add L to the number. If you do so then the leafname of the attached file or directory will appear on the button underneath the sprite. You will probably need to make the button much wider to accommodate the leafname. DialDelay Double-clicking with SELECT on a field containing a phone number whose tag or descriptor contains the string TEL (case insensitive) will attempt to produce dial-tones through the internal speaker. If you hold a telephone handset where it can the tones the number will be dialled. This Config option allows you to specify the delay in second between the double-click and the tones. (Default=5) TimeFirst determines the interpretation of incomplete entries in Time fields (see 4.2.5 ) Default=H (hours first), alternative=S (seconds first) time entry, interpretation of times, how displayed FullTime Either always display as hh:mm:ss (default=YES) or allow mm:ss for times under 1 hour (NO) Output If 1 (default) send output for Printer destination directly to printer. If 2 write first to file, then copy to printer. The latter may be needed to overcome problems with certain combinations of hardware and versions of !Printers. Config files for individual databases Individual databases may be provided with their own Config files but Fields BTime LeftOpen may only be set in Powerbase own Config file and will affect all databases. BackGnd be set for an individual database but will affect only the background colours of field descriptors, not the window colour itself. Where choices are valid they override Powerbase s own settings. Config files 14.6.1 Pathnames for External file objects External fields, pathnames of files The OLD method of deriving the pathname for the stored file object is as follows: Divide the record number by 4900. The integer part of the result is used as the name of the top-level directory. Divide the record number by 70. The integer part of the result is used as the name of the second-level directory. The integer remainder of dividing the record number by 70 is used as the actual filename. This is an awkward system if you are in the habit of using the filer windows to sift through the files rather than relying on Powerbase to display them. To calculate the number of the record to which the file belongs you have to take the number forming the name of the file s parent directory, multiply by 70 and add the number corresponding to the filename itself. The new system improves on this. Steps (a) and (b) are identical. The filename itself, however, uses the whole record number preceded by , e.g. a file associated with record 275 will be called Rec275 . You can therefore use the RISC OS filer command to locate and examine the file. Under the OLD system such a file would be called The default setting in Config is NEW. Users should be aware that the earliest version of Powerbase to support this feature is v. 6.94 , dated 02-03-96. This and later versions understand both methods of storage and will rename files according to the Config setting when a record is displayed . Thus, a database created under an earlier version in which record 275 is represented by a file called will have that file renamed to Rec275 simply by displaying the record , even if the file itself is not loaded. Once this has happened the file cannot be found by an earlier version of Powerbase . Under most circumstances this should not cause any difficulty; just make sure you are using v. 6.94 or later. Should you wish to run a database under an earlier version and access the External file objects from the record window you must first convert the filenames to the OLD format. To do so place OLD in the Config file as described above, then load Powerbase . Open your database and click on the Play button. Click on when all records have been displayed. If you use more than one subfile you will need to do this for each occupied subfile. OLD may be converted to NEW pathnames in a similar way. Messages file This text file in the Resources sub-directory contains all the error and warning messages used by Powerbase including those for Acorn s interactive application. The wording may be altered if you wish. Many error messages contain the string and possibly and . Data items are substituted for these when the message is printed so don t omit them or alter their logical placement within the message. c ~w7 B ~ 7 Appendix A Powerbase as an Impulse server The following is a description of the Impulse methods understood by Powerbase , given in the standard format recommended by Computer Concepts. Users wishing to write their own Impulse tasks to communicate with Powerbase will need this information. They will also need details of the SWIs to which Impulse will respond, and this information is obtainable from Computer Concepts. {methods: GetPathname Selection <string> ParseQuery <string> GetField <tag> GetRecord PutRecord ExpandCode <string> GetExpanded <string> NextMatch Description: GetPathname specifies an object i.e. a Powerbase database. If Powerbase has the required database open it replies with the full pathname of the object, otherwise it returns an error message. Example of use: :Powerbase !Elements GetPathname (returns pathname of !Elements R6 points to pathname when calling task is decoding reply. Selection <string> tells Powerbase about a field, or group of fields, in which the caller is interested. The fields are specified as a list of , separated by any suitable character (e.g. ). The same separator must appear at the very end of the tag-list. Powerbase replies with the maximum data-length of the combined fields in the selection. Example of use: Selection Impulse command :Powerbase Selection NAME/SYM/Z/ R6 points to a string which gives data-length. ParseQuery <string> informs Powerbase of the criteria to be used in selecting records to transmit to the caller. The parameter is a standard Powerbase search formula and Powerbase replies with the title which would normally appear at the head of one of its printed lists. Example of use: :Powerbase ParseQuery GP=T R6 points to returned title. GetField <tag> requests from Powerbase the data in the specified field of the next record which matches the preceding ParseQuery command. When Powerbase replies to the GetField command the calling task should respond with an Impulse_ FetchData, specifying the address and length of the buffer to be used, and then wait for an Impulse _Receive event (reason code &204) before reading the buffer. Example of use: :Powerbase GetField NAME GetRecord [<key>] (The square brackets signify that the key is optional: they are not part of the command.) Sent without a key it requests from Powerbase the next record which matches the preceding ParseQuery command. If the primary key of a record in the database is appended then Powerbase will return the record corresponding to that key. The key must be separated from the GetRecord by a space. The calling task must follow the procedure described above for GetField. Powerbase will transmit the fields specified in the Selection command, using the same separator as was used in that command. Receipt of data may be followed by another GetRecord command, returning the next matching record and so on, a zero-length response signifying that there are no more matching records. If using to request specific records you should end by sending *** as a key to tell Powerbase that the dialogue is finished. This command and the one below operate only on Subfile 0 of the Powerbase database. Example of use: :Powerbase GetRecord ACTI PutRecord informs Powerbase that the caller wishes to write a record to the current Powerbase database. It should be sent as message type &200. A Selection command should have been previously sent to tell Powerbase what fields to expect and what separator is to be used. Powerbase will then reply with a GetRecord command (reason code &201) which the caller should acknowledge with message type &202, specifying the maximum length of the data which will be sent. Wait for event &203 which is a request by Powerbase for the caller to transmit the data. The caller should then do so with Impulse _TransmitData, specifying the buffer address and the length of data being sent. This may be immediately followed by another PutRecord. ExpandCode <string> requests the expanded form of a code used in a field linked to a validation table . The parameter string consists of the code itself, then a space, then the name of the validation table with the number of the linked column appended. Example of use: :Powerbase ExpandCode T Group0 GetExpanded <string> combines the functions of GetField and ExpandCode, i.e. it requests the expanded form of the contents of a specified field. The parameter is the tag of the field. It is not necessary to supply the name of the validation table or number of the linked column since Powerbase can determine these from the entry in the file linking the field to the table. By default the second column of the table (i.e. column 1) is used to supply the expanded string, but this may be overrridden by appending the relevant column number preceded by a comma as in the second example. Remember that the first column is numbered 0. Examples of use: :Powerbase GetExpanded GP :Powerbase GetExpanded GP,2 NextMatch is designed to remove the restriction whereby only the data from a single record can be merged into a given document. All it does is tell Powerbase to access the next record matching the search formula in the Merge window and interpret subsequent Merge commands by taking data from that record. Appendix B Keystroke equivalents Only the function keys in the following list are re-assignable (see ); the Ctrl-letter keys are fixed. Next record in subfile Next record button Fast forward (default: 10 records) Shift Fast forward button Final record in subfile Final record button Previous record in subfile Previous record button Fast rewind (default: 10 records) Shift Fast rewind button Rewind button First record in subfile First record button Force update Select next subfile Next subfile button Select previous subfile Previous subfile button Rotate subfiles Rotate subfiles button Subfile rotation Select next key Shift Next key button Select previous key Shift Previous key button Shift record forward Shift forward button Shift record backward Shift back button Delete record Delete button Add new record Add record button Copy displayed record Shift Search for key in current subfile Search button Search for key in all subfiles Shift filter Filter switch List validation table for field with caret Turn validation ON/OFF Shift Play (rapid scan of records) Stop (cancel above) Shift List values Show keypad Print report Print Print displayed record Shift Print Display Print Options window Print Select all fields for printing Balance current index & display brief results As above, but display compelete index tree Shift B Copy field contents to clipboard Enter current date Edit record template Display list of fields, or of selected fields Open Changes dialogue box for current field Display Indices directory Open Index dialogue box for current field Show structure of currently-selected key Open dialogue box for current field Toggle mark-pane ON/OFF Open Numeric field calculations window Retrieve last search formula Display PrintJobs directory Display ValTables directory Display PrintRes directory Choose field at which editing starts Enter current time Blank field Paste field contents at caret Restore keypad & record windows to initial state Export CSV file Clear field selection Add new row to scrollable list (when in last cell) Shift Return As above, but from any cell & without scrolling Insert Delete blank row from scollable list Shift Insert Delete row with caret from scrollable list Shift Insert Index !BMARGIN !BODYFONT !CASE !CHANGE !CLEAR !COMMENT <string> !COPIES !CSV !DATE !DELETE !DESTINATION !EXCLUDE !EXPAND !FILTER !FIRST !FONTSIZE !FORMAT !HEADER, !FOOTER !HEADERFONT !HEADINGS !IMPRESSION 55-56 !INCLUDE !INDEX !INSERT !LABEL !LINESPACE !LMARGIN !MESSAGE !MOVE !OBEY !ORIENTATION !PAGE !PMARGINS !PRINTOPTS !QUERY !RMARGIN !SAVE !SCRIPT !SELECTION !SHRINK !SORT !SPACER !STARTAT !SUBFILES !TABLE !TEXTWIDTH !TITLE !TMARGIN !UPPER Access-control list Accumulating records in one subfile Add record button , 58, 65 Adding new records Allowed list in validation table 36-39 Alphanumeric fields Altering existing records Analysing Date fields by month Analysing indexed fields AND in search formulae AND, OR and NOT in Help window Application shell, creating Archives, unpacking Arithmetic in search and replace Arithmetic on report columns Arrow keys, action when editing Auto-balancing of indices Average, calculation for numeric column Balancing indices Batch deletion and moving of records Binary Large Objects (BLOBs) Blanking records on deletion Bookmarks , use of to mark place in file Bounding box of field, moving and re-sizing Browsing Button fields on record window Calculated fields 40-42 Calculation formulae Calculations, general discussion Calculations involving dates Calculations involving times Calculations, making retrospective Case of letters and indexing Case switch (on Query panel) 19, 26 switch (on Preferences window) Case-sensitive queries Changing keys Changing many records at once Changing size of database Changing subfile Check Boxes 18, 27, , 42, 47 Check boxes, 3-state Check boxes, appearance in reports Choosing which fields to print Clipboard, global Clipboard, use of Closing a database Collecting records in one subfile Colours used to identify key fields 8, 61 Colour used to identify linked fields 37, 61 Colour used to identify mandatory fields 33, 61 Colours window 8, 33, 37, Column calculations Column headings in reports Column spacer Comma, literal, including in search formulae Compacting sequence numbers Comparators in search formulae 19-20 Comparators, list of Comparison by numeric value, forcing Composite fields Computed fields 18, 27, 31, 40-42 Concatenation of fields Confidential data 13-14 Config files, description of 61-62 Config files for individual databases Copy Paste buttons Copying a field to the clipboard Copying fields Counting matching records Counting values in column CSV data, truncation of CSV field separator CSV file, conversion to functioning database CSV file, edits saved as CSV files 14, 28, 38, 44-48 , 51, 53, 57-58 CSV files, description of CSV files from scrollable lists CSV files from validation tables CSV files, importing into scrollable lists CSV files, importing into validation tables CSV header record CSV line terminator CSV options 44-45 , 47, 58-60 CSV options, importance of CSVoptions file Data length for record field 27-28 , 32, 50 Data length in scrollable lists Data Merge window Data overflow during CSV import Data underflow during CSV import Data-Merging with other applications 48, 60 Database 33, 43 Database file 7, 14, 31, 50, 59 Database size 33, 46, Database, adding new fields Database, reformatting Database, renaming Database, shortening using subset Date fields Date and time fields Date separator Date stamp fields Date stamp10 fields Date stamp8 fields Date stamp in report header Day-of-week stamp field Default database, creating 33, 27 Default print options file ( !PrintOpts Default Query file ( !Query Default selection file ( !Selection Delete button 11, 65 Deleting a batch of records Deleting fields during record design Deleting records Descriptors 27-29 , 43-44, 46, 50, 55, 59, 61 Dial-tones, generating from records Directory button Directory button, unlinking Disabling certain types of functionality 53, 58 Displayed record, exporting Displayed record, printing Doubled comparators in search formulae Draw files in external fields 14, 30-31 Duplicate keys, warning of Duplicate primary keys, listing of Duplicate primary keys, prohibiting Editable fields 18, 27, 33 Editing scrollable lists Editing, choosing starting field 12, 60 Email address for Powerbase Support Empty database, building Exact match in table validation 37, 38 Excluding individual records from reports Exit button on record window Expanding data using validation table 25, 39 Expanding report headers ExpandCode ( Impulse command) 63-64 Exporting data as CSV files Exporting subsets of records External fields 11, 14, 27, 30-31 , 46-47, 60 External fields, editing External fields, exporting External fields, linking files to External fields, pathnames of files External fields, unlinking 14, 31 External fields, warning of deletion from Extra button fields on record window Fast forward button Fast forward interval Fast rewind button Field, meaning of Field concatenation Field creation in record design Field Definition window 27-28, 50 Field selection for creating reports Field separator in CSV files Fields, copying Fields, making invisible and inaccessible Fields maximum number allowed Fields, printable types Fields, re-ordering File destination for reports File (CSV), conversion to working database Filters Filter switch Filetype &7f3, for print election Filetype &7f4, for ueries Filetype &7f5, for rint options Final record button First record button Fixed data on new records Flashing field, meaning of Footers in reports 25, 42, 55 Force update button , 37, 43, 60, 65 Form file 33, 50 Formats for reports Function keys 7, 53, 58 Function keys, assigning Function keys, disabling 53, 58 Function keys, storing strings in GetExpanded ( Impulse command) 63-64 GetField ( Impulse command) GetPathname ( Impulse command) GetRecord ( Impulse command) Global changes 12-13 Global clipboard, support for Grid for designing record layout Hardware requirements to run Powerbase Headers in reports 25, 46, 55 Header record in CSV file application Help available within Powerbase 6, 23 Help window for constructing search formulae Helpreader text Hiding sensitive data Horizontal format for reports 16-17 , 25, 55 Hyphen in search formulae I.D.s and personal passwords Iconbar menu Iconbar menu, disabling 53, 58 Importing data from CSV files Importing data from plain text files Impression , merging data into 48-49 , 56, 60 Impulse commands 48-49, 60, 63-64 Including individual records Inclusive search Index, case-sensitive Index, analysing Indexing a field Indices 7-9, 11, 25-27, 35, , 50-53, 59-60 Indices directory Inserting fields when designing record Installing Powerbase Internet special field type Invisible fields Key fields 7-10, 33-35 Key Structure 33-35 , 43, 50 Key structure of the currently-active index Key structure, examples 34-35 Key structure, multiple fields in Key, explanation of Key, inclusion in reports Keypad , 19, 32, 37-38, 43, 52-53, 58, 60, 65 Keypad buttons as record fields Keypad, disabling 53, 58 Keys 7-9, 34-35 , 51, 53, 58, 63 Keys Maximum number Keystrokes 7, 58 Label format Labels, including primary key Labels, number of lines printed Labels, substituting for blank field Labels, using up part sheets Last altered (stamp field) Launch new copy Line spacing, for tables Line-spacing Linked fields Linking tables to fields List values button 10, 37-38, 65 Loading Powerbase Loading validation tables Logging changes Log file Logo fields 31-32 Lookup function Mail-Merging 48-49 Main menu, disabling 53, 58 Manager access rights Manager password Mandatory fields 10, 33 Mandatory fields, colouring of Margins in reports 16, 25, 55 Marking records for printing etc. Marking records with bookmarks Match window 18-19, 23-24, 32, 54 Matches found during key search, number of Maximum value in column of numbers Maximum value in numeric fields Menu button on screen Menus directory for user menus 27, 33 Merging databases Messages file Minimum value in numeric fields Minimum value in column of numbers Month of year stamp field Months breakdown for Date fields Moving a batch of records Multi-criterion searches Multiple subfiles, use of Multitasking within Powerbase Naming subfiles New fields, adding to database New record format Next key button Next record button Next subfile button NextMatch ( Impulse command) 63-64 NOT in search formulae Null keys in subsidiary indices Null tags, where permitted Numeric fields 13, 25, 29, 35-36, 40-42 Numeric fields in search formulae Numeric fields, justification of Numeric min., start value for Sequence no button on Query panel button on Search window button on Access window Opening a database Options, CSV Options, print 16-17, 23-25 Order of fields in reports OR in search formulae 19-20, 26 Output destination for reports Ovation , mail-merging with 48-49 Page length in reports Page numbers in reports ParseQuery ( Impulse command) Password protection 6, 50, 52-53 , 58, 61 Paste from Clipboard Personal I.D.s and passwords Picture fields 14, 30-31 Place in subfile, storing Play button Pop-up menu Powerbase, versions and compatibility Preferences 10, 38, 43, 51, 53, 59-60 Preferences window Previous key button Previous record button Previous subfile button Primary key , 11, 17, 27, 33-35 , 43, 47, 50-51, 53, 60-61, 63 Primary key index Primary key, defining structure PrimaryKey file 33-35, 43, 50 Print button (on screen) Print destination 16-17 , 23, 25, 54-56, 62 Print formats 16-18, 24-25 Print Options 16, 24, 26, 39, 44, 46, 58-59 Print options files Print options window, displaying 23-24 Print Resources Print selections Printable fields Printer destination Printing arbitrary selection of records Printing from a Report window Printing order of selected fields Printing speed PrintJobs directory 16, 27, 38, 45-46, 54, 57, 65 PrintRe s directory 18, 27, 65 PutRecord ( Impulse command) 63-64 Query by example (QBE) 22-23 Query by example, limitations of 22-23 Query file, default Query files Query panel , 23, 26, 45, 54, 57 Quit button on record window Quitting Powerbase Quotation marks in CSV files RAM disc Random selection of records, printing Re-ordering fields during record design Read password Read/write password Recalculate on opening Record edits as CSV file Record format, minor adjustments to Record layout, designing Record numbers 7, 31 Record numbers, inclusion in reports Record, copying fields Record, copying entire Record terminator in CSV file , 45-46 Record window 7, 9-10, 14, 27-28, 30-32, 35, 39, 45-47, 49, 58, 60 Records, filtering Records, retrieving by key Records, retrieving by record number Records, retrieving via Report window Records, searching all subfiles for Records, searching for Related records, accessing Remote fields 30-31 Removing fields during record design Renaming a database Re-ordering fields during record design Replace-on-entry (validation tables) 37-38 Report printing 16, 53 Reporting, disabling Reports, formats for Resources Reverse order for reporting Reverse switch on Query panel Rewind button 8, 65 Rotate button 9, 65 Rotating subfiles Run file button on record window Run file button, unlinking Save box 16, 27, 38, 58 Saving from a Report window Saving a print selection Saving a query for future use 21-22 Saving print options Script commands 54-56 Script files 54-56 Script files, requesting user input from Script language Scrollable list, adding rows Scrollable list, clearing Scrollable list, deleting rows Scrollable list, exporting from Scrollable list, importing to Scrollable lists 18, 24, 27, 29 Scrollable lists and CSV files Scrollable lists and validation tables Scrollable lists, editing Scrollable lists, maximum number of Scrollable lists, printing format Scrollable lists, rows visible Scrollable lists, unequal columns in Scrollable lists, where to use Search all subfiles Search and replace Search button 9, 58, 65 Search element 19, 23 Search formula 10, 12-13, 18-23 , 25, 27, 45, 54, 57, 63-64 Search formulae and Help window Search window Searching for individual records 9-10, 43 Searching by filter Selection ( Impulse command) Self-calculating fields 40, 42 Separator in CSV files 44-45 Sequence number fields Shift back button Shift forward button Shift record forward or backward Shrinking reports (removing white space) 16, 25 Snap interval for record-design grid Sorting 16, 38, 43 Sorting a validation table Sorting lines in a Report window Sorting, specifying before printing Spacer between columns of reports , 45, 55 Speeding up printing Sprite files in External fields 14, 18, 30-31 Stamp fields 18, 27, 31-32 Stamp fields, equivalent Computed types 32, 40, 59 Standard deviation, calculation for column Start editing at specified field Stop button Strip leading and trailing spaces Subfile number, inclusion in reports Subfile rotation 9, 65 Subfiles 7, 9-10, 40, 50-51, 53, 60, 65 Subfiles, explanation of Subfiles, naming Subfiles, printing from several Subset database 19, 24, 51, 53, Subsidiary indices Subsidiary keys 7-8, Sum of numbers in report column Swap with button (field definition window) Table format for reports , 25, 55 Tables, maximum number permitted Tag list in search formula Tags 19-20, 23, 27-28, 34, 40-41, 43-44, 46, 48, 50, 55, 63 Tags, importance of Target list in search formula Template, use in data-entry Terminator in CSV files 44-45 Text block 14, 17-18, 25, 28, 30-31, 46 Text button on record window Text files in External fields 14, 30-31 Text width in Vertical format 17, 25 Three-state check-boxes 18, 30 TIME$ in user functions 40-42 Time entry, interpretation of Time fields 29, 31, 41 Time fields, calculations on Time fields, interpretation of input 29, 62 Time fields, separator in Time for report creation Time Stamp fields 31-32 Times, how displayed Title on reports TooBig file and CSV import, explanation of TSV files Undo facilities 11, 13, 38 Undoing mistakes Unlinking objects from External fields Unlinked validation tables, how to load Unrestricted input fields Upper case in reports 25, 29 User functions 41-42 User menus, defining User menus, where stored UserFuncs file Using CSV files to modify existing records Using more than one subfile UsrSprites Validate input button (Preferences) 59-60 Validation 10-12, 25, 36-39, 41, 43, 52, 55, 59-60, 64-65 Validation string in ValStrings file Validation table, creating Validation table data in printouts Validation table, displaying Validation table, saving contents as CSV file Validation tables, exact match with Validation tables, loading unlinked Validation tables, replace on entry feature Validation, turning ON/OFF ValStrings ValTables directory 27, 38-39, 65 Vertical format for reports 17, 25, 55 Website for information & upgrades White space in reports, removing Wild cards in search formulae Wild cards in search-and-replace Wild-cards, characters used to represent Window destination for reports Writable icons, copying between Write password Year stamp field Yes/No/Maybe fields VLZ X Vp> X V@d X V`, X Vh* X V@2 X V($ X 1_Plain